﻿//Description:批次加工记录类
//Copyright (c) : 通力凯顿（北京）系统集成有限公司
//Writer:Wangjh
//create Date:2017-6-26
//Rewriter:
//Rewrite Date:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using UFC.DBUtility;
using UFC.Common;
using System.Configuration;
using System.Data.OracleClient;
using System.Xml;
using External.APICalling;
using System.IO;
using System.Collections;
using System.Drawing;
using System.Text.RegularExpressions;
using BusinessRule.Common;
//using API.Userattribute;

namespace TransferContainerStepRecord
{
    public class TransferContainerStepRecordMethod
    {
        public void RunStepRecordSync()
        {
            //派工，报工
            System.Threading.Thread thDispacthAndReport = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    InsertDispachInfo();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("插入结构件派工信息", e);
                }
                try
                {
                    UpdateWorkReport();//已包含自动报工
                }
                catch (Exception myError)
                {
                    LogHelp.WriteError("报工信息", myError);
                }
            }));
            thDispacthAndReport.Start();
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thDispacthAndReport.ThreadState != System.Threading.ThreadState.Stopped);//插入自动报工数据

            //自动报工
            System.Threading.Thread thNoDispacthReport = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    InsertReportNoDispatch();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("插入自动报工数据", e);
                }
            }));
            thNoDispacthReport.Start();

            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thNoDispacthReport.ThreadState != System.Threading.ThreadState.Stopped);//插入自动报工数据      

            //结构件报工、检验和完工线程是否完成
            System.Threading.Thread thMachineOfJgj = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {

                try
                {
                    UpdateCheckInfo();
                }
                catch (Exception myError)
                {
                    LogHelp.WriteError("检验信息", myError);
                }
                try
                {
                    UpdateCompleteInfoOfJgj();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("插入结构件完工信息", e);
                }
            }));
            thMachineOfJgj.Start();
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thMachineOfJgj.ThreadState != System.Threading.ThreadState.Stopped);



        }

        public void RunStepRecordFix()
        {
            System.Threading.Thread thChangeQtyOfJgj = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    UpdateQtyOfSplit();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("更新分批导致的数量变化", e);
                }
            }));
            thChangeQtyOfJgj.Start();

            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thChangeQtyOfJgj.ThreadState != System.Threading.ThreadState.Stopped);//分批后数量变化的线程是否完成


            System.Threading.Thread thUpdateReportQty = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    UpdateReportQty();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("更新报工数量", e);
                }
            }));
            thUpdateReportQty.Start();
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thUpdateReportQty.ThreadState != System.Threading.ThreadState.Stopped);//更新报工数变化的记录


            System.Threading.Thread thRevertContainer = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    RevertContainer();
                    updateContainerStripNum();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("更新无效流水卡", e);
                }
            }));
            thRevertContainer.Start();
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thRevertContainer.ThreadState != System.Threading.ThreadState.Stopped);//更新报工数变化的记录





        }

        public void ClearInvalidData()
        {

            System.Threading.Thread thInValidCheck = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    InValidCheck(false);
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("无效的检验记录", e);
                }
                try
                {
                    InValidReport(false);
                }
                catch (Exception myError)
                {
                    LogHelp.WriteError("无效报工", myError);
                }
                try
                {
                    InValidDispatch(false);
                }
                catch (Exception myError)
                {
                    LogHelp.WriteError("无效派工", myError);
                }

                try
                {
                    RevertContainer();
                }
                catch (Exception myError)
                {
                    LogHelp.WriteError("删除无效流水卡", myError);
                }

            }));
            thInValidCheck.Start();
            do
            {
                System.Threading.Thread.Sleep(1000);
            } while (thInValidCheck.ThreadState != System.Threading.ThreadState.Stopped);//删除无效的附件

            System.Threading.Thread thInvalidDoc = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    //DeleteInvalidDoc();//附件没存在数据库中了
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("删除无效附件", e);
                }
                try
                {
                    DeleteRepeat();
                }
                catch (Exception e)
                { LogHelp.WriteError("删除重复检测项", e); }

                try
                {
                    deleteCheckPointTemp();
                }
                catch (Exception myError)
                { LogHelp.WriteError("无效检测项附件", myError); }
            }));
            thInvalidDoc.Start();
            do
            {
                System.Threading.Thread.Sleep(1000);
            } while (thInvalidDoc.ThreadState != System.Threading.ThreadState.Stopped);//删除无效的附件


            System.Threading.Thread thSurpassBillMapping = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    DeleteRepeatSurpassBill();
                }
                catch (Exception e)
                { LogHelp.WriteError("删除重复超越单", e); }

                try
                {
                    DeleteSurpassBillMapping();
                }
                catch (Exception e)
                { LogHelp.WriteError("删除超期无效的超越单映射关系", e); }

                try
                {
                    InsertSurpassBillMapping();
                }
                catch (Exception e)
                { LogHelp.WriteError("插入新建批次的超越单映射关系", e); }

            }));
            thSurpassBillMapping.Start();

            do
            {
                System.Threading.Thread.Sleep(1000);
            } while (thSurpassBillMapping.ThreadState != System.Threading.ThreadState.Stopped);//插入新建批次的超越单映射关系

        }

        //闲时处理数据
        public void ClearNightData()
        {
            System.Threading.Thread thInvalidDoc = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    //DeleteInvalidDoc();//附件没存在数据库中了
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("删除无效附件", e);
                }
                try
                {
                    DeleteRepeat();
                }
                catch (Exception e)
                { LogHelp.WriteError("删除重复检测项", e); }

                try
                {
                    deleteCheckPointTemp();
                }
                catch (Exception myError)
                { LogHelp.WriteError("无效检测项附件", myError); }
            }));
            thInvalidDoc.Start();

            //处理班组人员中的重复人员数据
            System.Threading.Thread thDealRepeatEmp = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    DealTeamEmp();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("处理班组中的重复人员", e);
                }
            }));
            thDealRepeatEmp.Start();


            //处理人员中的重复设备数据
            System.Threading.Thread thDealRepeatRes = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    DealEmpRes();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("处理人员中的重复设备", e);
                }
            }));
            thDealRepeatRes.Start();

            //处理isuse=1的派报检记录,一年前
            System.Threading.Thread thNoUseProcessing = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    DeleteNoUseProcessing();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("删除无效的派报检记录，一年前", e);
                }
            }));
            thNoUseProcessing.Start();

            //线程等待
            do
            {
                System.Threading.Thread.Sleep(1000);
            } while (thInvalidDoc.ThreadState != System.Threading.ThreadState.Stopped);//删除无效的附件
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thDealRepeatEmp.ThreadState != System.Threading.ThreadState.Stopped);//处理班组人员中的重复人员数据
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thDealRepeatRes.ThreadState != System.Threading.ThreadState.Stopped);//处理人员中的重复设备
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thNoUseProcessing.ThreadState != System.Threading.ThreadState.Stopped);//删除无效的派报检记录，一年前
        }

        //超越单处理
        public void RunSurpassBill()
        {

            System.Threading.Thread thSurpassBillMapping = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    DeleteRepeatSurpassBill();
                }
                catch (Exception e)
                { LogHelp.WriteError("删除重复超越单", e); }

                try
                {
                    DeleteSurpassBillMapping();
                }
                catch (Exception e)
                { LogHelp.WriteError("删除超期无效的超越单映射关系", e); }

                try
                {
                    InsertSurpassBillMapping();
                }
                catch (Exception e)
                { LogHelp.WriteError("插入新建批次的超越单映射关系", e); }

            }));
            thSurpassBillMapping.Start();

            do
            {
                System.Threading.Thread.Sleep(1000);
            } while (thSurpassBillMapping.ThreadState != System.Threading.ThreadState.Stopped);//插入新建批次的超越单映射关系

        }

        #region 公共方法 create:Wangjh
        /// <summary>
        /// 更新表的结转标记
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private Boolean UpdateTransferOfTable(string tableName, string ID, object IDValue)
        {
            string updateSql = @"UPDATE " + tableName + " T SET ISTRANSFER=1 WHERE T." + ID + "=:ID";
            OracleParameter[] updateParam = new OracleParameter[1];
            updateParam[0] = OracleHelper.NewParameter(":ID", IDValue, OracleType.VarChar);
            int i = OracleHelper.ExecuteSql(updateSql, updateParam);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        #endregion


        #region 添加派工信息 create:Wangjh 2017-7-17
        /// <summary>
        /// 插入结构件的派工信息到总表
        /// </summary>
        /// <returns></returns>
        public Boolean InsertDispachInfo()
        {
            DataTable dispachData = GetDispachInfo();
            if (dispachData.Rows.Count == 0)
            {
                return false;
            }
            List<object[]> sqllist = new List<object[]>();
            foreach (DataRow dr in dispachData.Rows)
            {
                InsertContainerDispachInfo(dr, 1, ref sqllist);
            }
            if (sqllist.Count > 0)
            {
                OracleHelper.ExecuteSqlTran(sqllist);
            }

            return true;
        }
        /// <summary>
        /// 获取结构件的派工信息
        /// update :ysj 2020-6-30
        /// </summary>
        /// <returns></returns>
        private DataTable GetDispachInfo()
        {
            //1天内数据
            string strSql = @"SELECT csd.factoryid,csd.containerid,c.containername,cl.containerlevelname CONTAINERLEVEL,p.productid,pb.productname,p.productrevision,
p.description Productdesc,ws.workflowstepname,ws.workflowstepid,s.specid,s.specno,wc.workcentername,wc.workcenterid,t.teamid,t.teamname,
csd.containerstepdispatchid AS DispatchID,csd.employeeid AS DispatchEmployeeID,e.fullname AS DispatchEmployeeName,
csd.qty AS DispatchQty,csd.resourceid AS DispatchResourceID,rf.description AS DispatchResourceName,csd.createdate AS DispatchDate,
wb.workflowname,w.workflowrevision,w.description workflowdesc,w.workflowid
FROM ( 
                        
SELECT csd.*  FROM containerstepdispatch csd 
 LEFT JOIN containersteprecord csr ON csr.dispatchid=csd.containerstepdispatchid
 WHERE csd.createdate>=sysdate-7 and csd.isuse=0 AND  nvl2(csr.dispatchid,'1','0')='0' and rownum<501
                         
 ) csd
INNER JOIN container c ON c.containerid=csd.containerid
LEFT JOIN containerlevel cl ON cl.containerlevelid=c.levelid  
LEFT JOIN product p ON p.productid=c.productid
LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
LEFT JOIN workflowstep ws ON ws.workflowstepid=csd.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
left join workflow w on w.workflowid=ws.workflowid
left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
LEFT JOIN workcenter wc ON wc.workcenterid=csd.workcenterid
left join team t on t.teamid=csd.teamid
LEFT JOIN employee e ON e.employeeid=csd.employeeid
LEFT JOIN resourcedef rf ON rf.resourceid=csd.resourceid
where c.status in (1,2) ";

            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];

        }

        private void InsertContainerDispachInfoByID(string dispatchid)
        {
            string strSql = string.Format(@" SELECT csd.factoryid,csd.containerid,c.containername,cl.containerlevelname CONTAINERLEVEL,p.productid,pb.productname,p.productrevision,
                     p.description Productdesc,ws.workflowstepname,ws.workflowstepid,s.specid,s.specno,wc.workcentername,wc.workcenterid,t.teamid,t.teamname,
                     csd.containerstepdispatchid AS DispatchID,csd.employeeid AS DispatchEmployeeID,e.fullname AS DispatchEmployeeName,
                     csd.qty AS DispatchQty,csd.resourceid AS DispatchResourceID,rf.description AS DispatchResourceName,csd.createdate AS DispatchDate,
                     wb.workflowname,w.workflowrevision,w.description workflowdesc,w.workflowid
                     FROM  ( SELECT csd.*  FROM containerstepdispatch csd 
                         LEFT JOIN containersteprecord csr ON csr.dispatchid=csd.containerstepdispatchid
                         WHERE csd.containerstepdispatchid='{0}') csd
                        INNER JOIN container c ON c.containerid=csd.containerid
                        LEFT JOIN containerlevel cl ON cl.containerlevelid=c.levelid	
                        LEFT JOIN product p ON p.productid=c.productid
                        LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
                        LEFT JOIN workflowstep ws ON ws.workflowstepid=csd.workflowstepid
                        LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                        LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                        left join workflow w on w.workflowid=ws.workflowid
                        left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
                        LEFT JOIN workcenter wc ON wc.workcenterid=csd.workcenterid
                        left join team t on t.teamid=csd.teamid
                        LEFT JOIN employee e ON e.employeeid=csd.employeeid
                        LEFT JOIN resourcedef rf ON rf.resourceid=csd.resourceid ", dispatchid);

            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return;
            }
            List<object[]> sqllist = new List<object[]>();
            DataTable dispachData = ds.Tables[0];
            if (dispachData.Rows.Count == 0)
            {
                return;
            }
            DataRow dr = dispachData.Rows[0];
            InsertContainerDispachInfo(dr, 1, ref sqllist);
            if (sqllist.Count > 0)
            {
                OracleHelper.ExecuteSqlTran(sqllist);
            }
        }


        //插入每行派工信息到工序加工情况表 type=0机匣 type=1结构件
        private void InsertContainerDispachInfo(DataRow dr, int type, ref  List<object[]> sqllist)
        {
            string insertSql = @" INSERT INTO ContainerStepRecord(Containerid,Containername,ContainerLevel,Productid,Productname,
                  Productrevision,Productdesc,workflowstepname,Specid,specNO,FactoryID,WorkCenterName,WorkCenterID,TeamName,
                  TeamID,DispatchID,DispatchEmployeeID,DispatchEmployeeName,DispatchQty,DispatchResourceID,DispatchResourceName,
                    DispatchDate,Usecompleted,usefulStatus,workflowstepid,workflowname,workflowrevision,workflowdesc,workflowid,CreateDate) 
                  VALUES(:Containerid,:Containername,:ContainerLevel,:Productid,:Productname,:Productrevision,:Productdesc,
                  :workflowstepname,:Specid,:specNO,:FactoryID,:WorkCenterName,:WorkCenterID,:TeamName,:TeamID,:DispatchID,
                  :DispatchEmployeeID,:DispatchEmployeeName,:DispatchQty,:DispatchResourceID,:DispatchResourceName,
                TO_DATE(:DispatchDate,'yyyy-mm-dd hh24:mi:ss'),0,1,:workflowstepid,:workflowname,:workflowrevision,:workflowdesc,:workflowid,sysdate)";
            OracleParameter[] insertPara = new OracleParameter[27];
            insertPara[0] = OracleHelper.NewParameter(":Containerid", dr["Containerid"], OracleType.VarChar);
            insertPara[1] = OracleHelper.NewParameter(":Containername", dr["Containername"], OracleType.VarChar);
            insertPara[2] = OracleHelper.NewParameter(":ContainerLevel", dr["ContainerLevel"], OracleType.VarChar);
            insertPara[3] = OracleHelper.NewParameter(":Productid", dr["Productid"], OracleType.VarChar);
            insertPara[4] = OracleHelper.NewParameter(":Productname", dr["Productname"], OracleType.VarChar);
            insertPara[5] = OracleHelper.NewParameter(":Productrevision", dr["Productrevision"], OracleType.VarChar);
            insertPara[6] = OracleHelper.NewParameter(":Productdesc", dr["Productdesc"], OracleType.VarChar);

            if (type == 0)
            {
                insertPara[7] = OracleHelper.NewParameter(":workflowstepname", dr["SPECNAME"], OracleType.VarChar);
            }
            else if (type == 1)
            {
                insertPara[7] = OracleHelper.NewParameter(":workflowstepname", dr["workflowstepname"], OracleType.VarChar);

            }
            insertPara[8] = OracleHelper.NewParameter(":Specid", dr["Specid"], OracleType.VarChar);
            insertPara[9] = OracleHelper.NewParameter(":specNO", dr["specNO"], OracleType.VarChar);
            insertPara[10] = OracleHelper.NewParameter(":FactoryID", dr["FactoryID"], OracleType.VarChar);

            insertPara[11] = OracleHelper.NewParameter(":WorkCenterName", dr["WorkCenterName"], OracleType.VarChar);
            insertPara[12] = OracleHelper.NewParameter(":WorkCenterID", dr["WorkCenterID"], OracleType.VarChar);

            insertPara[13] = OracleHelper.NewParameter(":TeamName", dr["TeamName"], OracleType.VarChar);
            insertPara[14] = OracleHelper.NewParameter(":TeamID", dr["TeamID"], OracleType.VarChar);

            insertPara[15] = OracleHelper.NewParameter(":DispatchID", dr["DispatchID"], OracleType.VarChar);
            insertPara[16] = OracleHelper.NewParameter(":DispatchEmployeeID", dr["DispatchEmployeeID"], OracleType.VarChar);
            insertPara[17] = OracleHelper.NewParameter(":DispatchEmployeeName", dr["DispatchEmployeeName"], OracleType.VarChar);
            insertPara[18] = OracleHelper.NewParameter(":DispatchQty", dr["DispatchQty"], OracleType.Number);
            insertPara[19] = OracleHelper.NewParameter(":DispatchResourceID", dr["DispatchResourceID"], OracleType.VarChar);
            insertPara[20] = OracleHelper.NewParameter(":DispatchResourceName", dr["DispatchResourceName"], OracleType.VarChar);
            insertPara[21] = OracleHelper.NewParameter(":DispatchDate", Convert.ToString(dr["DispatchDate"]), OracleType.VarChar);
            if (type == 0)
            {
                insertPara[22] = OracleHelper.NewParameter(":workflowstepid", string.Empty, OracleType.VarChar);
            }
            else if (type == 1)
            {
                insertPara[22] = OracleHelper.NewParameter(":workflowstepid", dr["workflowstepid"], OracleType.VarChar);
            }
            insertPara[23] = OracleHelper.NewParameter(":workflowname", dr["workflowname"], OracleType.VarChar);
            insertPara[24] = OracleHelper.NewParameter(":workflowrevision", dr["workflowrevision"], OracleType.VarChar);
            insertPara[25] = OracleHelper.NewParameter(":workflowdesc", dr["workflowdesc"], OracleType.VarChar);
            insertPara[26] = OracleHelper.NewParameter(":workflowid", dr["workflowid"], OracleType.VarChar);
            sqllist.Add(new object[2] { insertSql, insertPara });
        }
        #endregion

        #region 添加报工信息 create:Wangjh 2017-7-17

        //更新所有报工信息,结构件
        //update :ysj 2020-6-30
        public Boolean UpdateWorkReport()
        {
            DataTable dt = GetWorkReportInfo();

            DataTable dispatchDt = dt.DefaultView.ToTable(true, "DispatchID");

            for (int i = 0; i < dispatchDt.Rows.Count; i++)
            {
                dt.DefaultView.RowFilter = string.Format("{0}='{1}'", "DispatchID", dispatchDt.Rows[i]["DispatchID"]);
                DataTable newDt = dt.Clone();
                newDt = dt.DefaultView.ToTable();
                //add:Wangjh 2017-8-1 优化
                int leng = newDt.Rows.Count;
                for (int j = 0; j < leng; j++)
                {
                    string dispatchid = Convert.ToString(newDt.Rows[j]["DispatchID"]);
                    if (UpdateWorkReport(newDt.Rows[j]) == 0)//等于0表示是新的报工数据需要插入再更新（一次派工多次报工情况）
                    {
                        InsertContainerDispachInfoByID(dispatchid);
                        //if (InsertNewRowOfSelect(dispatchid) == false)
                        //{
                        //    continue;
                        //}
                        if (UpdateWorkReport(newDt.Rows[j]) < 0)
                        {
                            continue;
                        }
                    }

                    // LogHelp.WriteLog("更新报工结束:" + newDt.Rows[j]["containersteprecordid"].ToString());
                }

            }
            return true;
        }

        //获取批次的报工信息,结构件
        private DataTable GetWorkReportInfo()
        {
            string strSql = @" SELECT distinct csw.containerstepdispatchid  as DispatchID,csw.containerstepworkreportid AS containersteprecordid,csw.factoryid,csw.teamid,t.teamname,csw.workerid AS ReportEmployeeID,e.fullname AS ReportEmployeeName,csw.createdate AS ReportDate ,csw.qty AS ReportQty,
                            w.workflowid,wb.workflowname,w.workflowrevision,w.description AS Workflowdesc,ws.workflowstepname,ws.workflowstepid
                            FROM (
                            
                            select csw.* from containerstepworkreport csw
                            left join containersteprecord csr on csr.dispatchid = csw.containerstepdispatchid 
                            inner join containercurrentinfo c on c.containerid=csw.containerid
                            where csw.createdate>=sysdate-7 and csw.isuse=0 
                            and nvl2(csr.dispatchid,'1','0')='1' AND NOT EXISTS (
                            SELECT 1 FROM containersteprecord csr WHERE csr.containersteprecordid=csw.containerstepworkreportid
                            ) and rownum<501
                                                  
                            ) csw
                            LEFT JOIN team t ON t.teamid=csw.teamid
                            LEFT JOIN employee e ON e.employeeid=csw.workerid
                            LEFT JOIN workflowstep ws ON ws.workflowstepid=csw.workflowstepid
                            LEFT JOIN workflow w ON w.workflowid=ws.workflowid
                            LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid";

            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }

        //更新每行的报工信息，结构件
        private int UpdateWorkReport(DataRow dr)
        {
            string updateSql = @" UPDATE containersteprecord csr SET ContainerStepRecordID=:ContainerStepRecordID,FactoryID=:FactoryID,TeamName=:TeamName,TeamID=:TeamID,
                                ReportEmployeeID=:ReportEmployeeID,ReportEmployeeName=:ReportEmployeeName,
                                ReportDate=TO_DATE(:ReportDate,'yyyy-mm-dd hh24:mi:ss'),ReportQty=:ReportQty,
                                WORKFLOWID=:WORKFLOWID,WORKFLOWNAME=:WORKFLOWNAME,WORKFLOWREVISION=:WORKFLOWREVISION,WORKFLOWDESC=:WORKFLOWDESC,WORKFLOWSTEPID=:WORKFLOWSTEPID,
                                WORKFLOWSTEPNAME=:WORKFLOWSTEPNAME WHERE DispatchID=:DispatchID and ContainerStepRecordID is null";
            OracleParameter[] updatePara = new OracleParameter[15];
            updatePara[0] = OracleHelper.NewParameter(":ReportEmployeeID", dr["ReportEmployeeID"], OracleType.VarChar);
            updatePara[1] = OracleHelper.NewParameter(":ReportEmployeeName", dr["ReportEmployeeName"], OracleType.VarChar);
            updatePara[2] = OracleHelper.NewParameter(":ReportDate", dr["ReportDate"], OracleType.VarChar);
            updatePara[3] = OracleHelper.NewParameter(":ReportQty", dr["ReportQty"], OracleType.Number);
            updatePara[4] = OracleHelper.NewParameter(":ContainerStepRecordID", dr["ContainerStepRecordID"], OracleType.VarChar);
            updatePara[5] = OracleHelper.NewParameter(":DispatchID", dr["DispatchID"], OracleType.VarChar);

            updatePara[6] = OracleHelper.NewParameter(":WORKFLOWID", dr["WORKFLOWID"], OracleType.VarChar);
            updatePara[7] = OracleHelper.NewParameter(":WORKFLOWNAME", dr["WORKFLOWNAME"], OracleType.VarChar);
            updatePara[8] = OracleHelper.NewParameter(":WORKFLOWREVISION", dr["WORKFLOWREVISION"], OracleType.VarChar);
            updatePara[9] = OracleHelper.NewParameter(":WORKFLOWDESC", dr["WORKFLOWDESC"], OracleType.VarChar);
            updatePara[10] = OracleHelper.NewParameter(":WORKFLOWSTEPID", dr["WORKFLOWSTEPID"], OracleType.VarChar);
            updatePara[11] = OracleHelper.NewParameter(":WORKFLOWSTEPNAME", dr["WORKFLOWSTEPNAME"], OracleType.VarChar);

            updatePara[12] = OracleHelper.NewParameter(":FactoryID", dr["FactoryID"], OracleType.VarChar);
            updatePara[13] = OracleHelper.NewParameter(":TeamName", dr["TeamName"], OracleType.VarChar);
            updatePara[14] = OracleHelper.NewParameter(":TeamID", dr["TeamID"], OracleType.VarChar);
            int i = OracleHelper.ExecuteSql(updateSql, updatePara);
            return i;
        }

        //  插入派工基本信息（用于同一派工下的多个报工情况）
        private Boolean InsertNewRowOfSelect(string dispachID)
        {
            string insertSql = @" INSERT INTO ContainerStepRecord(Containerid,Containername,ContainerLevel,Productid,Productname,Productrevision,Productdesc,Workflowid,
                                          Workflowname,workflowrevision,Workflowdesc,workflowstepname,Workflowstepid,Specid,specNO,FactoryID,WorkCenterName,WorkCenterID,TeamName,
                                          TeamID,DispatchID,DispatchEmployeeID,DispatchEmployeeName,DispatchQty,DispatchResourceID,DispatchResourceName,DispatchDate,Usecompleted,usefulStatus,CreateDate) 
                                          select Containerid,Containername,ContainerLevel,Productid,Productname,Productrevision,Productdesc,Workflowid,
                                          Workflowname,workflowrevision,Workflowdesc,workflowstepname,Workflowstepid,Specid,specNO,FactoryID,WorkCenterName,WorkCenterID,TeamName,
                                          TeamID,DispatchID,DispatchEmployeeID,DispatchEmployeeName,DispatchQty,DispatchResourceID,DispatchResourceName,DispatchDate,Usecompleted,usefulStatus,CreateDate FROM CONTAINERSTEPRECORD";
            insertSql += string.Format(" where DispatchID='{0}' AND ROWNUM=1", dispachID);
            int i = OracleHelper.ExecuteSql(insertSql);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;

            }
        }
        #endregion

        #region 添加批次工序检验信息 create:Wangjh 2017-7-18
        //获取检验信息
        private DataTable GetContianerCheckInfo()
        {
            string strSql = @"SELECT csr.containerid,csr.workflowstepid,csw.CheckEmployeeID,csw.DefectQty,csw.ReworkQty,csw.LossQty,csw.CheckDate, e.fullname CheckEmployeeName,
csw.passqty,csw.passqty2,csr.containersteprecordid,CSW.WORKERID Reportemployeeid,E2.FULLNAME Reportemployeename,CSW.FACTORYID
FROM (

SELECT cci.checkerid CheckEmployeeID,cci.defectqty DefectQty,cci.ReworkQty,NVl(cci.acceptanceqty,csw.qty-NVL(csw.lossqty,0)) passqty,nvl(cci.acceptanceqty2,0) passqty2,
cci.createdate CheckDate,csw.WORKERID,CSW.FACTORYID, (NVL(csw.lossqty,0)+NVL(csw.SpecimenLossQty,0)) LossQty,
CSW.CONTAINERSTEPWORKREPORTID FROM CONTAINERSTEPWORKREPORT CSW 
  INNER JOIN containercheckinfo  cci ON  CCI.CONTAINERSTEPWORKREPORTID=CSW.CONTAINERSTEPWORKREPORTID  
  WHERE (cci.createdate>=SYSDATE-1 or (csw.checktype=25 and csw.createdate>=sysdate-10) or (cci.factoryid='0004e2800000281c' and  cci.createdate>=sysdate-30) )   AND cci.isuse=0 AND  NVL2(csw.containerfinishedcheckinfoid,'1','0')='0'                 
UNION ALL
SELECT cfci.checkerid CheckEmployeeID,cfci.defectqty DefectQty,cfci.ReworkQty,NVl(cfci.acceptanceqty,csw.qty-NVL(csw.lossqty,0)) passqty,nvl(cfci.acceptanceqty2,0) passqty2,
cfci.createdate CheckDate,csw.WORKERID,CSW.FACTORYID, (NVL(csw.lossqty,0)+NVL(csw.SpecimenLossQty,0)) LossQty,
CSW.CONTAINERSTEPWORKREPORTID FROM containerfinishedcheckinfo cfci 
INNER JOIN CONTAINERSTEPWORKREPORT CSW  ON  CFCI.containerfinishedcheckinfoID=CSW.containerfinishedcheckinfoID
WHERE cfci.createdate>=SYSDATE-1 AND cfci.isuse=0 AND   NVL2(csw.containerfinishedcheckinfoid,'1','0')='1'

) csw
INNER JOIN containersteprecord csr ON CSW.CONTAINERSTEPWORKREPORTID=CSR.CONTAINERSTEPRECORDID                           
LEFT JOIN employee e ON e.employeeid=csw.CheckEmployeeID
LEFT JOIN EMPLOYEE E2 ON E2.EMPLOYEEID=CSW.WORKERID
inner join containercurrentinfo c on c.containerid=csr.containerid
WHERE csr.USECOMPLETED=0 AND (nvl2(csr.checkdate,'1','0') ='0' OR csw.lossqty<>nvl(csr.lossqty ,0))
AND csr.usefulstatus=1 ";
            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }

        //更新所有检验信息
        public Boolean UpdateCheckInfo()
        {
            DealRepeatStepRecord();
            DataTable dt = GetContianerCheckInfo();
            if (dt.Rows.Count == 0)
            {
                return false;
            }
            List<object[]> sqllist = new List<object[]>();
            foreach (DataRow dr in dt.Rows)
            {
                UpdateContianerCheckInfoOfRow(dr, 1, ref sqllist);
                string updateSql = string.Format("Update containercheckinfo Set ISTRANSFER=1 where   CONTAINERSTEPWORKREPORTID='{0}'", dr["Containersteprecordid"]);
                sqllist.Add(new object[] { updateSql, null });
            }
            if (sqllist.Count > 0)
            {
                OracleHelper.ExecuteSqlTran(sqllist);
            }
            //完工处理，有检验信息更新，可能有完工标记需要更新 add:Wangjh 20221224
            DataView dv = dt.DefaultView;
            DataTable stepDT = dv.ToTable(true, "containerid", "workflowstepid");
            foreach (DataRow stepRow in stepDT.Rows)
            {
                DataTable completeDt = GetCompleteInfoOfJgj(stepRow["containerid"].ToString(), stepRow["workflowstepid"].ToString());
                if (completeDt.Rows.Count > 0)
                {
                    UpdateCompleteInfoOfRow(completeDt.Rows[0], completeDt.DefaultView);
                }
            }
            return true;
        }

        //更新每行的检验信息 type=0机匣 type=1 结构件
        private void UpdateContianerCheckInfoOfRow(DataRow dr, int type, ref List<object[]> sqllist)
        {
            string updateSql = @" UPDATE ContainerStepRecord SET CheckEmployeeID=:CheckEmployeeID,CheckEmployeeName=:CheckEmployeeName,PassQty=:PassQty,
                                          LossQty=:LossQty,CheckDate=to_date(:CheckDate,'yyyy-mm-dd hh24:mi:ss')";//注意这里以报工对应主线ID为条件

            if (type == 1)
            {
                updateSql += string.Format(",REPORTEMPLOYEEID='{0}'", dr["REPORTEMPLOYEEID"].ToString());
                updateSql += string.Format(",Reportemployeename='{0}'", dr["Reportemployeename"].ToString());
                updateSql += string.Format(",FACTORYID='{0}'", dr["FACTORYID"].ToString());
            }
            updateSql += string.Format(",DefectQty='{0}'", dr["DefectQty"].ToString() == "0" ? string.Empty : dr["DefectQty"].ToString());
            updateSql += string.Format(",ReworkQty='{0}'", dr["ReworkQty"].ToString() == "0" ? string.Empty : dr["ReworkQty"].ToString());
            updateSql += "  WHERE Containersteprecordid=:Containersteprecordid";
            OracleParameter[] updatePara = new OracleParameter[6];
            updatePara[0] = OracleHelper.NewParameter(":CheckEmployeeID", dr["CheckEmployeeID"], OracleType.VarChar);
            updatePara[1] = OracleHelper.NewParameter(":CheckEmployeeName", dr["CheckEmployeeName"], OracleType.VarChar);
            if (Convert.ToInt16(dr["PassQty"].ToString()) < 0) { dr["PassQty"] = 0; }//为负的情况是试件报工
            updatePara[2] = OracleHelper.NewParameter(":PassQty", dr["PassQty2"], OracleType.Number);//这里的合格数排除了超差数和返修数
            updatePara[3] = OracleHelper.NewParameter(":LossQty", dr["LossQty"], OracleType.Number);
            updatePara[4] = OracleHelper.NewParameter(":CheckDate", dr["CheckDate"], OracleType.VarChar);
            if (type == 0)
            {
                updatePara[5] = OracleHelper.NewParameter(":Containersteprecordid", dr["HISTORYMAINLINEID"], OracleType.VarChar);
            }
            else if (type == 1)
            {
                updatePara[5] = OracleHelper.NewParameter(":Containersteprecordid", dr["Containersteprecordid"], OracleType.VarChar);
            }

            sqllist.Add(new object[2] { updateSql, updatePara });

        }
        #endregion

        #region 查询是否完工 create:Wangjh 2017-7-18
        //更新所有的完工信息,结构件
        public Boolean UpdateCompleteInfoOfJgj()
        {
            DataTable dt = GetCompleteInfoOfJgj("", "");
            if (dt.Rows.Count == 0)
            {
                return false;
            }
            DataView dv = dt.DefaultView;
            DataTable stepDT = dv.ToTable(true, "containerid", "workflowstepid");

            foreach (DataRow dr in stepDT.Rows)
            {

                if (UpdateCompleteInfoOfRow(dr, dv) == false)
                {
                    return false;
                }
            }

            return true;
        }

        //查询所有完工信息
        private DataTable GetCompleteInfoOfJgj(string containerId, string stepId)
        {
            string strSql = @" SELECT a.sumQty,csr.containerid,csr.workflowstepid,c.qty,csr.checkemployeeid,nvl(csr.passqty,0) passqty,csw.checktype,nvl(csw.specimenqty,0) specimenqty FROM containersteprecord csr
                                    INNER JOIN container c ON c.containerid=csr.containerid
                                    INNER JOIN (                                    
                                    SELECT SUM( csr.passqty+nvl(csr.reworkqty,0)+nvl(csr.defectqty,0)) sumQty,csr.containerid,csr.workflowstepid FROM containersteprecord csr 
                                                 WHERE csr.containerid='{0}' and csr.workflowstepid='{1}'  and csr.usecompleted=0
                                                 and  nvl2(csr.checkdate,'1','0')='1'
                                    GROUP BY csr.containerid,csr.workflowstepid 
                                    
                                    ) A ON A.containerid=CSR.CONTAINERID AND A.workflowstepid=CSR.WORKFLOWSTEPID
                                    LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid=csr.containersteprecordid
                                    
                                    WHERE  nvl2(csr.checkdate,'1','0')='1'
                                    AND ( A.sumQty>=C.QTY OR (A.sumQty >=0  AND (csw.checktype=4 OR csw.checktype=3 or csw.specimenqty is not null)) ) 
                                    AND csr.usefulstatus=1 AND CSR.USECOMPLETED=0 ";

            strSql = string.Format(strSql, containerId, stepId);
            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }


        //更新一个批次每一个工序的完工信息
        private Boolean UpdateCompleteInfoOfRow(DataRow dr, DataView dv)
        {
            dv.RowFilter = string.Format("containerid='{0}' and workflowstepid='{1}'", dr["containerid"], dr["workflowstepid"]);
            DataTable checkdt = dv.ToTable();
            int sumqty = int.Parse(checkdt.Rows[0]["sumQty"].ToString());
            DataRow row = checkdt.Rows[0];
            int qty = int.Parse(row["qty"].ToString());
            if (string.IsNullOrWhiteSpace(row["checktype"].ToString()))
                return true;
            if (sumqty > 0 && sumqty < qty)
            {
                if (row["specimenqty"].ToString() == "0")//试件数
                {
                    if (row["checktype"].ToString() == "1" || row["checktype"].ToString() == "2")
                        return true;
                }
            }

            string updateSql = "UPDATE containersteprecord SET Usecompleted=1 WHERE containerid=:containerid AND workflowstepid=:workflowstepid";

            OracleParameter[] updatePara = new OracleParameter[2];
            updatePara[0] = OracleHelper.NewParameter(":containerid", dr["containerid"], OracleType.VarChar);
            updatePara[1] = OracleHelper.NewParameter(":workflowstepid", dr["workflowstepid"], OracleType.VarChar);
            int i = OracleHelper.ExecuteSql(updateSql, updatePara);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        #endregion

        #region 删除无效流水卡

        public void RevertContainer()
        {
            string strSql = @" SELECT distinct C.CONTAINERID,C.STATUS FROM CONTAINER C 
                            inner JOIN containersteprecord csr ON csr.CONTAINERID=C.CONTAINERID
                            WHERE C.STATUS=0"; // 
            DataSet ds = OracleHelper.Query(strSql);
            DataTable statusDt;
            if (ds == null || ds.Tables.Count == 0)
            {
                return;
            }
            statusDt = ds.Tables[0];
            string delSql = @" DELETE containersteprecord csr WHERE csr.CONTAINERID = :CONTAINERID";

            foreach (DataRow dr in statusDt.Rows)
            {
                if (Convert.ToInt64(dr["STATUS"]) == 0)//批次删除
                {
                    OracleParameter[] deleteParam = new OracleParameter[1];
                    deleteParam[0] = OracleHelper.NewParameter(":CONTAINERID", dr["CONTAINERID"], OracleType.VarChar);
                    int i = OracleHelper.ExecuteSql(delSql, deleteParam);
                    if (i <= 0)
                    {
                        return;
                    }
                }

            };

            /*
             * 删除无效流水卡的排产信息 add by zhangrj 20241219
             */
            strSql = @"SELECT DISTINCT c.containerid FROM container c
                        INNER JOIN apsplaninfo ai ON ai.containerid = c.containerid
                        WHERE c.status = 0 AND ROWNUM <= 1000"; //每次删除一千条
            ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return;
            }
            DataTable dtApsCon = ds.Tables[0];
            foreach (DataRow item in dtApsCon.Rows)
            {
                OracleHelper.ExecuteSql(string.Format(@"DELETE apsplandetailemptime apet WHERE apet.apsplandetailempid IN (
                                                    SELECT ape.apsplandetailempid FROM apsplandetailemp ape WHERE ape.apsplandetailid IN (SELECT ap.apsplandetailid FROM apsplandetail ap WHERE ap.containerid = '{0}')
                                                    )", item["containerid"].ToString()));
                OracleHelper.ExecuteSql(string.Format("DELETE apsplandetailemp ape WHERE ape.apsplandetailid IN (SELECT ap.apsplandetailid FROM apsplandetail ap WHERE ap.containerid = '{0}')", item["containerid"].ToString()));
                OracleHelper.ExecuteSql(string.Format("DELETE apsplandetail ap WHERE ap.containerid = '{0}'", item["containerid"].ToString()));
                OracleHelper.ExecuteSql(string.Format("DELETE apsplaninfo ai WHERE ai.containerid = '{0}'", item["containerid"].ToString()));
                OracleHelper.ExecuteSql(string.Format("DELETE apsplandetail2 ap WHERE ap.containerid = '{0}'", item["containerid"].ToString()));
            }
        }

        #endregion

        #region 更新子序号数量和批次在制数不一致的批次 add:Wangjh 20241202

        public void updateContainerStripNum()
        {
            string sql = @"SELECT c.containerid,c.containername,c.status,c.qty,SUM(cc.qty) subQty FROM  container c 
LEFT JOIN container cc ON cc.parentcontainerid=c.containerid AND cc.qty>0
WHERE c.containertype=3 AND c.status!=0 
GROUP BY c.containerid,c.containername ,c.status,c.qty
HAVING (SUM(cc.qty)!=c.qty)";

            DataTable dt = OracleHelper.Query(sql).Tables[0];

            foreach (DataRow dr in dt.Rows)
            {
                ArrayList sqlList = new ArrayList();
                sqlList.Add(string.Format("UPDATE container c SET c.qty={0} WHERE c.containerid='{1}'", dr["subQty"], dr["containerid"].ToString()));
                sqlList.Add(string.Format("UPDATE containercurrentinfo c SET c.qty={0} WHERE c.containerid='{1}'", dr["subQty"], dr["containerid"].ToString()));
                OracleHelper.ExecuteSqlTran(sqlList);
            }
        }

        #endregion

        #region 更新由于分批数量信息
        /// <summary>
        /// 更新所有由于分批导致的数量变化
        /// </summary>
        public void UpdateQtyOfSplit()
        {
            DataTable dt = GetChangeQtyOfContainer();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    if (UpdateQtyInfoOfRow(dr) == false)
                    {
                        return;
                    }
                }
            }
        }
        /// <summary>
        /// 获取在制数变化的批次
        /// </summary>
        /// <returns></returns>
        private DataTable GetChangeQtyOfContainer()
        {
            //            string strSql = @" SELECT C.CONTAINERNAME,C.QTY,CCI.QTY AS CCIQTY,CCI.CONTAINERID,csi.containersplitinfoid,CSW.CONTAINERSTEPWORKREPORTID,
            //                            NVL( chec.containercheckinfoid,cfci.containerfinishedcheckinfoid) checkID,CSD.QTY DISPATCHQTY,CSW.QTY REPORTQTY,NVL(CSW.LOSSQTY,0) LOSSQTY,NVL(NVL(CFCI.ACCEPTANCEQTY,CHEC.ACCEPTANCEQTY),0) PASSQTY,NVL(NVL(CFCI.DEFECTQTY,CHEC.DEFECTQTY),0) DEFECTQTY  FROM CONTAINER C
            //                            INNER JOIN CONTAINERCURRENTINFO CCI ON CCI.CONTAINERID=C.CONTAINERID
            //                            LEFT JOIN containersplitinfo csi ON csi.containerid=cci.containerid AND csi.workflowstepid=cci.workflowstepid AND CSI.ISUSE=0
            //                            LEFT JOIN CONTAINERSTEPWORKREPORT CSW ON CSW.CONTAINERID=CSI.CONTAINERID AND CSW.WORKFLOWSTEPID=CSI.WORKFLOWSTEPID AND CSW.ISUSE=0--报工
            //                            LEFT JOIN containercheckinfo  chec ON chec.containerstepworkreportid=csw.containerstepworkreportid AND chec.isuse=0--工序检验
            //                            LEFT JOIN CONTAINERFINISHEDCHECKINFO CFCI ON CFCI.CONTAINERID=CSW.CONTAINERID AND CFCI.WORKFLOWSTEPID=CSW.WORKFLOWSTEPID AND CFCI.ISUSE=0--成检
            //                            LEFT JOIN containersteprecord csr ON csr.containersteprecordid=csw.containerstepworkreportid
            //                            LEFT JOIN CONTAINERSTEPDISPATCH CSD ON CSD.CONTAINERSTEPDISPATCHID=CSW.CONTAINERSTEPDISPATCHID--派工
            //                            WHERE CSI.CONTAINERSPLITINFOID IS NOT NULL 
            //                            AND (csw.qty<>csr.reportqty OR CSD.QTY<>CSR.DISPATCHQTY OR NVL(CFCI.ACCEPTANCEQTY,chec.acceptanceqty)<>CSR.PASSQTY)
            //                            GROUP BY C.CONTAINERNAME,C.QTY,CCI.QTY ,CCI.CONTAINERID,csi.containersplitinfoid,CSW.CONTAINERSTEPWORKREPORTID,chec.containercheckinfoid,cfci.containerfinishedcheckinfoid ,CSD.QTY ,CSW.QTY ,CSW.LOSSQTY,CFCI.ACCEPTANCEQTY,CHEC.ACCEPTANCEQTY,CFCI.DEFECTQTY,CHEC.DEFECTQTY
            //                                ";
            string strSql = @"SELECT C.CONTAINERNAME,C.QTY,CCI.QTY AS CCIQTY,CCI.CONTAINERID,csi.containersplitinfoid,CSW.CONTAINERSTEPWORKREPORTID,
      NVL( chec.containercheckinfoid,cfci.containerfinishedcheckinfoid) checkID,CSD.QTY DISPATCHQTY,CSW.QTY REPORTQTY,
      NVL(CSW.LOSSQTY,0) LOSSQTY,NVL(NVL(CFCI.ACCEPTANCEQTY,CHEC.ACCEPTANCEQTY),0) PASSQTY,
      NVL(NVL(CFCI.DEFECTQTY,CHEC.DEFECTQTY),0) DEFECTQTY  FROM  containersplitinfo csi
       INNER JOIN CONTAINERCURRENTINFO CCI ON csi.containerid=cci.containerid AND csi.workflowstepid=cci.workflowstepid AND CSI.ISUSE=0
      INNER JOIN  CONTAINER C ON CCI.CONTAINERID=C.CONTAINERID
      LEFT JOIN CONTAINERSTEPWORKREPORT CSW ON CSW.CONTAINERID=CSI.CONTAINERID AND CSW.WORKFLOWSTEPID=CSI.WORKFLOWSTEPID AND CSW.ISUSE=0--报工
      LEFT JOIN containercheckinfo  chec ON chec.containerstepworkreportid=csw.containerstepworkreportid AND chec.isuse=0--工序检验
      LEFT JOIN CONTAINERFINISHEDCHECKINFO CFCI ON CFCI.Containerfinishedcheckinfoid=CSW.Containerfinishedcheckinfoid  AND CFCI.ISUSE=0--成检
      LEFT JOIN containersteprecord csr ON csr.containersteprecordid=csw.containerstepworkreportid
      LEFT JOIN CONTAINERSTEPDISPATCH CSD ON CSD.CONTAINERSTEPDISPATCHID=CSW.CONTAINERSTEPDISPATCHID--派工
      WHERE csi.CREATEDATE >SYSDATE-1 
      AND (csw.qty<>csr.reportqty OR CSD.QTY<>CSR.DISPATCHQTY OR NVL(CFCI.ACCEPTANCEQTY,chec.acceptanceqty)<>CSR.PASSQTY)";
            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }
        /// <summary>
        /// 更新数量信息,由于分批导致的报工检验等数量变化
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private Boolean UpdateQtyInfoOfRow(DataRow dr)
        {
            string updateSql = @" UPDATE CONTAINERSTEPRECORD CSR SET CSR.DISPATCHQTY=:DISPATCHQTY,CSR.REPORTQTY=:REPORTQTY,CSR.LOSSQTY=:LOSSQTY,CSR.PASSQTY=:PASSQTY,CSR.DEFECTQTY=:DEFECTQTY
                                WHERE CSR.CONTAINERSTEPRECORDID=:CONTAINERSTEPRECORDID";

            OracleParameter[] updatePara = new OracleParameter[6];
            updatePara[0] = OracleHelper.NewParameter(":DISPATCHQTY", dr["DISPATCHQTY"], OracleType.Number);
            updatePara[1] = OracleHelper.NewParameter(":REPORTQTY", dr["REPORTQTY"], OracleType.Number);
            updatePara[2] = OracleHelper.NewParameter(":LOSSQTY", dr["LOSSQTY"], OracleType.Number);
            updatePara[3] = OracleHelper.NewParameter(":PASSQTY", dr["PASSQTY"], OracleType.Number);
            updatePara[4] = OracleHelper.NewParameter(":DEFECTQTY", dr["DEFECTQTY"], OracleType.Number);
            updatePara[5] = OracleHelper.NewParameter(":CONTAINERSTEPRECORDID", dr["CONTAINERSTEPWORKREPORTID"], OracleType.VarChar);

            int i = OracleHelper.ExecuteSql(updateSql, updatePara);

            if (i > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region 增加无派工的报工记录
        /// <summary>
        /// 获取所有没有派工记录的报工信息
        /// </summary>
        /// <returns></returns>
        DataTable GetReportInfoOfNoDispatch()
        {
            string strSql = @"SELECT c.containerid,c.containername,e.employeeid reportemployeeID,e.fullname reportemployeeName,csw.createdate reportDate,csw.qty AS ReportQty,
                                csw.containerstepworkreportid AS containersteprecordid,w.workflowid,wb.workflowname,w.workflowrevision,
                                w.description workflowdesc,ws.workflowstepid,ws.workflowstepname,csw.factoryid,t.teamid,t.teamname,
cl.containerlevelname ContainerLevel,p.productid,pb.productname,p.productrevision,p.description productdesc,s.specid,
                                s.specno,wc.workcenterid,wc.workcentername FROM containerstepworkreport csw 
                                LEFT JOIN container c ON c.containerid=csw.containerid
                                LEFT JOIN containerlevel cl ON cl.containerlevelid=c.levelid
                                LEFT JOIN product p ON p.productid=c.productid
                                LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
                                LEFT JOIN employee e ON e.employeeid=csw.workerid
                                LEFT JOIN workflowstep ws ON ws.workflowstepid=csw.workflowstepid
                                LEFT JOIN workflow w ON w.workflowid=ws.workflowid
                                LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
                                LEFT JOIN team t ON t.teamid=csw.teamid
                                LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                                LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                                LEFT JOIN workcenter wc ON wc.workcenterid=c.workcenterid
                                LEFT JOIN containersteprecord csr ON csr.containersteprecordid=csw.containerstepworkreportid
                                WHERE csw.createdate>=SYSDATE-100  AND c.status IN (1,2) AND  csw.isuse=0 AND nvl2(csr.containersteprecordid,'1','0')='0'
                                AND NVL2(csw.containerstepdispatchid,'1','0')='0' and rownum<501 ";
            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];

        }
        /// <summary>
        /// 更新每条无派工的报工记录
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private Boolean InsertReportNoDispatchOfRow(DataRow dr)
        {
            string insertSql = @"
                                INSERT INTO containersteprecord csr (reportemployeeID,reportemployeeName,reportDate,ReportQty,containersteprecordid,
                                workflowid,workflowname,workflowrevision,workflowdesc,workflowstepid,workflowstepname,factoryid,teamid,teamname,
                                ContainerLevel,productid,productname,productrevision,productdesc,specid,specno,workcenterid,workcentername,containername,containerid,usefulstatus,usecompleted,CreateDate)
                                VALUES(:reportemployeeID,:reportemployeeName,TO_DATE(:reportDate,'yyyy-mm-dd hh24:mi:ss'),:ReportQty,:containersteprecordid,:workflowid,:workflowname,
                                :workflowrevision,:workflowdesc,:workflowstepid,:workflowstepname,:factoryid,:teamid,:teamname,:ContainerLevel,:productid,
                                :productname,:productrevision,:productdesc,:specid,:specno,:workcenterid,:workcentername,:containername,:containerid,1,0,sysdate)
                                ";
            OracleParameter[] insertPara = new OracleParameter[25];
            insertPara[0] = OracleHelper.NewParameter(":reportemployeeID", dr["reportemployeeID"], OracleType.VarChar);
            insertPara[1] = OracleHelper.NewParameter(":reportemployeeName", dr["reportemployeeName"], OracleType.VarChar);
            insertPara[2] = OracleHelper.NewParameter(":reportDate", dr["reportDate"], OracleType.VarChar);
            insertPara[3] = OracleHelper.NewParameter(":ReportQty", dr["ReportQty"], OracleType.VarChar);
            insertPara[4] = OracleHelper.NewParameter(":containersteprecordid", dr["containersteprecordid"], OracleType.VarChar);
            insertPara[5] = OracleHelper.NewParameter(":workflowid", dr["workflowid"], OracleType.VarChar);
            insertPara[6] = OracleHelper.NewParameter(":workflowname", dr["workflowname"], OracleType.VarChar);
            insertPara[7] = OracleHelper.NewParameter(":workflowrevision", dr["workflowrevision"], OracleType.VarChar);
            insertPara[8] = OracleHelper.NewParameter(":workflowdesc", dr["workflowdesc"], OracleType.VarChar);
            insertPara[9] = OracleHelper.NewParameter(":workflowstepid", dr["workflowstepid"], OracleType.VarChar);
            insertPara[10] = OracleHelper.NewParameter(":workflowstepname", dr["workflowstepname"], OracleType.VarChar);
            insertPara[11] = OracleHelper.NewParameter(":factoryid", dr["factoryid"], OracleType.VarChar);
            insertPara[12] = OracleHelper.NewParameter(":teamid", dr["teamid"], OracleType.VarChar);
            insertPara[13] = OracleHelper.NewParameter(":teamname", dr["teamname"], OracleType.VarChar);
            insertPara[14] = OracleHelper.NewParameter(":ContainerLevel", dr["ContainerLevel"], OracleType.VarChar);
            insertPara[15] = OracleHelper.NewParameter(":productid", dr["productid"], OracleType.VarChar);
            insertPara[16] = OracleHelper.NewParameter(":productname", dr["productname"], OracleType.VarChar);
            insertPara[17] = OracleHelper.NewParameter(":productrevision", dr["productrevision"], OracleType.VarChar);
            insertPara[18] = OracleHelper.NewParameter(":productdesc", dr["productdesc"], OracleType.Number);
            insertPara[19] = OracleHelper.NewParameter(":specid", dr["specid"], OracleType.VarChar);
            insertPara[20] = OracleHelper.NewParameter(":specno", dr["specno"], OracleType.VarChar);
            insertPara[21] = OracleHelper.NewParameter(":workcenterid", Convert.ToString(dr["workcenterid"]), OracleType.VarChar);
            insertPara[22] = OracleHelper.NewParameter(":workcentername", Convert.ToString(dr["workcentername"]), OracleType.VarChar);
            insertPara[23] = OracleHelper.NewParameter(":containername", Convert.ToString(dr["containername"]), OracleType.VarChar);
            insertPara[24] = OracleHelper.NewParameter(":containerid", Convert.ToString(dr["containerid"]), OracleType.VarChar);
            int i = OracleHelper.ExecuteSql(insertSql, insertPara);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新无派工的报工记录
        /// </summary>
        public void InsertReportNoDispatch()
        {
            DataTable dt = GetReportInfoOfNoDispatch();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    if (InsertReportNoDispatchOfRow(dr) == false)
                    {
                        return;
                    }
                }
            }

        }
        #endregion

        #region  重复报工处理

        private void DealRepeatStepRecord()
        {
            DataTable RepeatData = GetRepeatStepRecord();
            if (RepeatData.Rows.Count == 0)
            {
                return;
            }

            foreach (DataRow dr in RepeatData.Rows)
            {
                string strSql = string.Format(@"select t.*, t.rowid from CONTAINERSTEPRECORD t
                            WHERE t.containersteprecordid='{0}'", dr["CONTAINERSTEPRECORDID"]);

                DataTable dt = OracleHelper.Query(strSql).Tables[0];
                if (dt.Rows.Count < 2)
                { return; }
                DataRow errDR = dt.Rows[0];
                strSql = string.Format(@"delete CONTAINERSTEPRECORD t WHERE t.rowid='{0}'", errDR["rowid"]);
                int i = OracleHelper.ExecuteSql(strSql);
            }
        }

        private DataTable GetRepeatStepRecord()
        {
            string strSql = @" SELECT * FROM（ SELECT COUNT(t.CONTAINERSTEPRECORDID) CNT,  t.CONTAINERSTEPRECORDID
                               FROM CONTAINERSTEPRECORD T     WHERE T.Reportdate>=SYSDATE-2                         
                              GROUP BY t.CONTAINERSTEPRECORDID）A     WHERE A.CNT > 1 ";

            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }

        #endregion

        #region 更新无效的报工记录

        /// <summary>
        /// 删除无效数据
        /// </summary>
        public void InValidReport(Boolean IsRealtime)
        {
            DataTable dt = GetNoUseReport(IsRealtime);
            if (dt.Rows.Count > 0)
            {
                List<object[]> sqllist = new List<object[]>();
                foreach (DataRow dr in dt.Rows)
                {
                    DeleteReportOfRow(dr, ref sqllist);
                    LogHelp.WriteLog("更新无效报工记录：" + dr["containersteprecordid"].ToString());
                }
                if (sqllist.Count > 0)
                {
                    OracleHelper.ExecuteSqlTran(sqllist);
                }
                dt = dt.DefaultView.ToTable(true, "dispatchid");
                foreach (DataRow dr in dt.Rows)
                {
                    string strSql = string.Format(@" SELECT * FROM containersteprecord WHERE dispatchid='{0}'", dr["dispatchid"]);
                    DataSet ds = OracleHelper.Query(strSql);
                    if (ds == null || ds.Tables.Count == 0)
                    {
                        continue;
                    }
                    if (ds.Tables[0].Rows.Count == 0)
                        InsertContainerDispachInfoByID(dr["dispatchid"].ToString());
                }
            }
        }
        /// <summary>
        /// 获取所有无效的报工记录
        /// </summary>
        /// <returns></returns>
        DataTable GetNoUseReport(Boolean IsRealtime)
        {
            //            string strSql = @"   SELECT csr.containersteprecordid,csr.factoryid FROM containersteprecord csr 
            //                            LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid=csr.containersteprecordid
            //                            LEFT JOIN factory fa ON fa.factoryid=csr.factoryid
            //                            WHERE (csw.containerstepworkreportid IS NULL or csw.isuse=1) AND csr.containersteprecordid IS NOT NULL
            //                           AND (fa.factoryname not like '%机匣%' OR FA.factoryname IS NULL OR (fa.factoryname LIKE '%机匣%' AND csr.reportdate>TO_DATE('2018/05/27','yyyy/MM/dd')) )
            //                            ";
            string strSql = string.Empty;
            strSql = @"SELECT csw.containerstepworkreportid containersteprecordid,csw.containerstepdispatchid dispatchid FROM containerstepworkreport csw 
WHERE csw.createdate>=sysdate-720  AND csw.isuse=1 
and exists (
select 1 from containersteprecord csr where csr.containersteprecordid=csw.containerstepworkreportid
)";

            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }
        /// <summary>
        /// 删除每行无效的报工记录
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private void DeleteReportOfRow(DataRow dr, ref List<object[]> sqllist)
        {
            string deleteSql = @"DELETE containersteprecord csr WHERE csr.containersteprecordid =:containersteprecordid";

            OracleParameter[] deletePara = new OracleParameter[1];
            deletePara[0] = OracleHelper.NewParameter(":containersteprecordid", dr["containersteprecordid"], OracleType.VarChar);
            sqllist.Add(new object[2] { deleteSql, deletePara });
        }

        #endregion

        #region 更新无效的派工记录
        /// <summary>
        /// 删除无效数据
        /// </summary>
        public void InValidDispatch(Boolean IsRealtime)
        {
            DataTable dt = GetNoUseDispatch(IsRealtime);
            if (dt.Rows.Count > 0)
            {
                List<object[]> sqllist = new List<object[]>();

                foreach (DataRow dr in dt.Rows)
                {
                    DeleteDispatchOfRow(dr, ref sqllist);
                    LogHelp.WriteLog("更新分厂无效派工记录" + dr["dispatchid"]);
                }
                if (sqllist.Count > 0)
                {
                    OracleHelper.ExecuteSqlTran(sqllist);
                }
            }
        }
        /// <summary>
        /// 获取所有无效的派工记录
        /// </summary>
        /// <returns></returns>
        DataTable GetNoUseDispatch(Boolean IsRealtime)
        {
            //            string strSql = @" 
            //                            SELECT csr.dispatchid,csr.factoryid FROM containersteprecord csr 
            //                            LEFT JOIN containerstepdispatch csd ON csd.containerstepdispatchid=csr.dispatchid
            //                            LEFT JOIN container c ON c.containerid=csr.containerid
            //                            LEFT JOIN factory fa ON fa.factoryid=c.originalfactoryid
            //                            WHERE csr.dispatchid IS NOT NULL AND (csd.containerstepdispatchid IS NULL or csd.isuse=1) 
            //                            AND (fa.factoryname not like '%机匣%' OR (fa.factoryname LIKE '%机匣%' AND csr.dispatchdate>TO_DATE('2018/05/27','yyyy/MM/dd')))
            //                            ";

            string strSql = string.Empty;
            strSql = @"select csd.containerstepdispatchid dispatchid from containerstepdispatch csd
where csd.createdate>=sysdate-720 and csd.isuse=1 and exists
(
select 1 from containersteprecord csr where csr.dispatchid=csd.containerstepdispatchid
) ";
            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }
        /// <summary>
        /// 删除每行无效的派工记录
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private void DeleteDispatchOfRow(DataRow dr, ref List<object[]> sqllist)
        {
            string deleteSql = @"DELETE containersteprecord csr WHERE csr.dispatchid =:dispatchid";

            OracleParameter[] deletePara = new OracleParameter[1];
            deletePara[0] = OracleHelper.NewParameter(":dispatchid", dr["dispatchid"], OracleType.VarChar);
            sqllist.Add(new object[2] { deleteSql, deletePara });

        }

        #endregion

        #region 更新报工数量变化的记录
        /// <summary>
        /// 获取所有无效的报工记录
        /// </summary>
        /// <returns></returns>
        DataTable GetChangeReportQty()
        {
            string strSql = @" 
                           SELECT csw.qty,csw.containerstepworkreportid FROM containerstepworkreport csw
                            INNER JOIN containersteprecord csr ON csr.containersteprecordid=csw.containerstepworkreportid
                            WHERE csr.reportqty<>csw.qty AND csw.createdate>SYSDATE-1 AND csw.isuse=0 ";
            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }
        /// <summary>
        /// 删除每行报工数量
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private Boolean UpdateReportQtyOfRow(DataRow dr)
        {
            string deleteSql = @"UPDATE containersteprecord csr SET csr.reportqty=:reportqty WHERE csr.containersteprecordid=:containersteprecordid";

            OracleParameter[] deletePara = new OracleParameter[2];
            deletePara[0] = OracleHelper.NewParameter(":containersteprecordid", dr["containerstepworkreportid"], OracleType.VarChar);
            deletePara[1] = OracleHelper.NewParameter(":reportqty", dr["qty"], OracleType.Number);
            int i = OracleHelper.ExecuteSql(deleteSql, deletePara);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        /// <summary>
        /// 删除无效数据
        /// </summary>
        void UpdateReportQty()
        {
            DataTable dt = GetChangeReportQty();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    if (UpdateReportQtyOfRow(dr) == false)
                    {
                        return;
                    }
                }
            }
        }


        #endregion

        #region 更新无效的检验记录

        /// <summary>
        /// 获取无效的检验记录
        /// </summary>
        /// <returns></returns>
        DataTable GetNoUseCheck(Boolean IsRealtime)
        {
            //            string strSql = @"
            //                            SELECT NULL CheckEmployeeID,NULL DefectQty,NULL CheckEmployeeName,NULL passqty,NULL LossQty,NULL CheckDate,csr.containersteprecordid,csr.factoryid,CSR.CONTAINERID||CSR.WORKFLOWSTEPID CONTAINERSTEP FROM containersteprecord csr 
            //                            LEFT JOIN containercheckinfo cci ON cci.containerid=csr.containerid AND cci.workflowstepid=csr.workflowstepid and cci.createdate=csr.checkdate                       
            //                            LEFT JOIN containerfinishedcheckinfo cfci ON cfci.containerid=csr.containerid AND cfci.workflowstepid=csr.workflowstepid and cfci.createdate=csr.checkdate 
            //                             LEFT JOIN FACTORY fa ON fa.factoryid=csr.factoryid
            //                            WHERE csr.usefulstatus=1 AND csr.checkemployeeid IS NOT NULL AND csr.checkdate IS NOT NULL
            //                             AND （fa.factoryname NOT LIKE '%机匣%' OR (fa.factoryname LIKE '%机匣%' AND csr.checkdate>TO_DATE('2018/05/27','yyyy/MM/dd')) OR FA.factoryname IS NULL） and(( cci.isuse IS NULL AND CFCI.ISUSE IS NULL) OR (cci.isuse=1 OR cfci.isuse=1))
            //                            ";

            string strSql = string.Empty;
            strSql = @"select CSR.CONTAINERID,CSR.WORKFLOWSTEPID,NULL CheckEmployeeID,NULL DefectQty,NULL CheckEmployeeName,NULL passqty,NULL LossQty,NULL CheckDate,csr.containersteprecordid,csr.factoryid,
CSR.CONTAINERID||CSR.WORKFLOWSTEPID CONTAINERSTEP,cci.containercheckinfoid from (

select cfci.containerid,cfci.workflowstepid,cfci.createdate checkdate,cfci.containerfinishedcheckinfoid containercheckinfoid,csw.containerstepworkreportid from containerfinishedcheckinfo cfci 
inner join containerstepworkreport csw on csw.containerfinishedcheckinfoid=cfci.containerfinishedcheckinfoid
where cfci.createdate>=sysdate-720 and cfci.isuse=1

union all

select cci.containerid,cci.workflowstepid,cci.createdate checkdate,cci.containercheckinfoid,cci.containerstepworkreportid from containercheckinfo cci 
where cci.createdate>=sysdate-720  and cci.isuse=1
) cci
inner join  containersteprecord csr on csr.containersteprecordid=cci.containerstepworkreportid and csr.checkdate=cci.checkdate ";
            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 更新每行撤销的检验记录
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private Boolean UpdateNoUseRecordOfRow(DataRow dr)
        {
            string updateSql = @" UPDATE ContainerStepRecord SET CheckEmployeeID=null,CheckEmployeeName=null,PassQty=null,
                                  LossQty=null,CheckDate=null,DefectQty=NULL,Usecompleted=0";//注意这里以报工对应主线ID为条件
            updateSql += "  WHERE Containersteprecordid=:Containersteprecordid";

            OracleParameter[] updatePara = new OracleParameter[1];
            updatePara[0] = OracleHelper.NewParameter(":Containersteprecordid", dr["Containersteprecordid"].ToString(), OracleType.VarChar);

            int i = OracleHelper.ExecuteSql(updateSql, updatePara);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新推送的记录为无效
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        Boolean UpdateWriteToErp(DataRow dr)
        {
            ArrayList sqlList = new ArrayList();
            sqlList.Add(@"UPDATE Writetoerprecord wt SET wt.isuse=1 WHERE wt.recordid='" + dr["ContainerStep"] + "'");
            sqlList.Add(@"UPDATE mes_workingreport_table wt SET wt.isuse=1,STATUS=0,reportdate=to_date(to_char(SYSDATE,'yyyy/MM/dd'),'yyyy/MM/dd') WHERE isuse=0 and wt.containerstep='" + dr["ContainerStep"] + "'");

            OracleHelper.ExecuteSqlTran(sqlList);
            return true;
        }

        /// <summary>
        /// 更新无效的检验记录
        /// </summary>
        public void InValidCheck(Boolean IsRealtime)
        {
            DataTable dt = GetNoUseCheck(IsRealtime);
            if (dt.Rows.Count > 0)
            {
                List<object[]> sqllist = new List<object[]>();
                string updateSql = string.Empty;
                foreach (DataRow dr in dt.Rows)
                {
                    UpdateNoUseRecordOfRow(dr);
                    //UpdateWriteToErp(dr); 已放在mes中

                    updateSql = string.Format("Update containercheckinfo Set ISTRANSFER=2 where isuse=1 and  CONTAINERSTEPWORKREPORTID='{0}'", dr["containersteprecordid"]);
                    sqllist.Add(new object[] { string.Format("update containersteprecord csr set csr.usecompleted=0 where CONTAINERID='{0}' and WORKFLOWSTEPID='{1}' ", dr["CONTAINERID"].ToString(), dr["WORKFLOWSTEPID"].ToString()), null });
                    sqllist.Add(new object[] { updateSql, null });


                    LogHelp.WriteLog("更新分厂无效检验记录" + dr["containersteprecordid"]);
                }
                if (sqllist.Count > 0)
                {
                    OracleHelper.ExecuteSqlTran(sqllist);
                }
            }
        }

        #endregion

        #region 删除无效附件，清除数据库内容
        /// <summary>
        /// 删除无效的附件记录
        /// </summary>
        void DeleteInvalidDoc()
        {
            //初始化api
            Dictionary<string, string> apiPara = new Dictionary<string, string>();
            apiPara.Add("UserName", ConfigurationManager.AppSettings["CamstarUserName"]);
            apiPara.Add("Password", ConfigurationManager.AppSettings["CamstarPassword"]);
            External.APICalling.CamstarClientAPI api = new External.APICalling.CamstarClientAPI(apiPara["UserName"], apiPara["Password"]);

            DataTable dt = GetNoUseDocument();//删除无用Document
            if (dt.Rows.Count > 0)
            {
                DataRow[] drs = dt.Select("IsRevofrcd=0");//其他版本

                foreach (DataRow dr in drs)
                {
                    if (DeleteNoUseDocOfRow(dr, api) == false)
                        continue;
                    dt.Rows.Remove(dt.Select("documentid='" + dr["documentid"].ToString() + "'")[0]);
                }

                DataRow[] drs2 = dt.Select("IsRevofrcd=1");//默认版本
                foreach (DataRow dr in drs2)
                {
                    if (dt.Select("DocName='" + dr["DocName"].ToString() + "'").Length == 1)
                    {
                        if (DeleteNoUseDocOfRow(dr, api) == false)
                            continue;
                    }
                }
            }
            dt = GetNoUseDocumentSet();//删除无用DocumentSet
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    if (DeleteNoUseDocOfRow(dr, api) == false)
                    {
                        continue;
                    }
                }
            }
        }
        /// <summary>
        /// 获取无效的附件集
        /// </summary>
        /// <returns></returns>
        DataTable GetNoUseDocumentSet()
        {
            string strSql = @"
                            SELECT ds.documentsetid,null Revision, ds.documentsetname DocName,'DocumentSetMaintDoc' TxnDocName ,'DocumentSetMaint' TxnName,'NDO' CDOType,0 IsRevofrcd FROM documentset ds
                            LEFT JOIN product p ON p.documentsetid=ds.documentsetid
                            LEFT JOIN spec s ON s.documentsetid=ds.documentsetid
                            LEFT JOIN workflow w ON w.documentsetid=ds.documentsetid
                            LEFT JOIN CHANGESTATUS CH ON CH.CHANGESTATUSID=DS.CHANGESTATUSID
                            WHERE ds.documentsetid IS NOT NULL AND p.documentsetid IS NULL AND s.documentsetid IS NULL AND w.documentsetid IS NULL AND CH.LASTCHANGEDATE>SYSDATE-1 AND ROWNUM<51 
                            ";
            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }
        /// <summary>
        /// 获取无效附件
        /// </summary>
        /// <returns></returns>
        DataTable GetNoUseDocument()
        {
            string strSql = @"
                            SELECT d.documentrevision Revision,db.documentname DocName,'DocumentMaintDoc' TxnDocName ,'DocumentMaint' TxnName,'RO' CDOType,(CASE WHEN db.revofrcdid=d.documentid THEN 1 ELSE 0 END) IsRevofrcd,d.documentid FROM DOCUMENT d
                            LEFT JOIN Documententry dt ON dt.documentid=d.documentid
                            LEFT JOIN documentbase db ON db.documentbaseid=d.documentbaseid
                            LEFT JOIN CHANGESTATUS CH ON CH.CHANGESTATUSID=D.CHANGEHISTORYID
                            WHERE dt.documentid IS NULL AND d.documentid IS NOT NULL AND CH.LASTCHANGEDATE>SYSDATE-1 AND ROWNUM<51
                            ";
            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 删除每条无效附件
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private Boolean DeleteNoUseDocOfRow(DataRow dr, External.APICalling.CamstarClientAPI api)
        {
            string strMsg = "";
            Boolean result = false;
            if (dr["IsRevofrcd"].ToString() == "1") //默认版本
            {
                result = api.DeleteModel(dr["TxnDocName"].ToString(), dr["TxnName"].ToString(), dr["DocName"].ToString(), dr["Revision"].ToString(), dr["CDOType"].ToString(), ref strMsg, true);
            }
            else if (dr["IsRevofrcd"].ToString() == "0")//其他版本
            {
                result = api.DeleteModel(dr["TxnDocName"].ToString(), dr["TxnName"].ToString(), dr["DocName"].ToString(), dr["Revision"].ToString(), dr["CDOType"].ToString(), ref strMsg);
            }
            LogHelp.WriteLog(strMsg);
            if (result)
                return true;
            else
                return false;
        }

        #endregion

        #region 插入新建批次的超越单映射信息

        /// <summary>
        /// 获取有超越单无新建批次映射信息数据
        /// </summary>
        /// <returns></returns>
        DataTable GetContainerNoRecordSurpass()
        {
            string strSql = @"
                            SELECT sd.surpassbillname,cci.containername,cci.containerid,sd.processno,sd.productname,sde.surpassop,sde.surpassbilldetailid FROM surpassbillinfo sd 
                            INNER JOIN containercurrentinfo cci ON cci.productname=sd.productname
                             INNER JOIN workflow w ON w.process_no=sd.processno AND w.workflowid=cci.workflowid
                            LEFT JOIN surpassbilldetail sde ON sde.surpassbillinfoid=sd.surpassbillinfoid
LEFT JOIN surpassbillmapping sm ON sm.surpassbilldetailid=sde.surpassbilldetailid AND sm.containerid=cci.containerid 
                            WHERE to_date(sd.effective,'yyyy-mm-dd hh24:mi:ss')>SYSDATE AND cci.containerid IS NOT NULL AND sm.containerid IS NULL and sde.surpassop is not null
                            GROUP BY sm.containerid,sd.surpassbillname,cci.containername,cci.containerid,sd.processno,sd.productname,sde.surpassop,sde.surpassbilldetailid
                            ";
            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }
        /// <summary>
        /// 插入每行超越单映射信息
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        Boolean InsertSurpassBillMappingOfRow(DataRow dr)
        {
            string insertSql = @"INSERT INTO surpassbillmapping (Containerid,Processno,Specno,Surpassbilldetailid) VALUES(:Containerid,:Processno,:Specno,:Surpassbilldetailid)";

            OracleParameter[] insertPara = new OracleParameter[4];
            insertPara[0] = OracleHelper.NewParameter(":Containerid", dr["Containerid"].ToString(), OracleType.VarChar);
            insertPara[1] = OracleHelper.NewParameter(":Processno", dr["Processno"].ToString(), OracleType.VarChar);
            insertPara[2] = OracleHelper.NewParameter(":Specno", dr["Specno"].ToString(), OracleType.VarChar);
            insertPara[3] = OracleHelper.NewParameter(":Surpassbilldetailid", dr["Surpassbilldetailid"].ToString(), OracleType.VarChar);

            int i = OracleHelper.ExecuteSql(insertSql, insertPara);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 插入超越单映射信息
        /// </summary>
        void InsertSurpassBillMapping()
        {
            DataTable dt = GetContainerNoRecordSurpass();
            dt.Columns.Add("Specno");
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    string[] strSpeclist = dr["surpassop"].ToString().Split(';');

                    foreach (string strSpec in strSpeclist)
                    {
                        int tempInt;
                        if (!int.TryParse(strSpec, out tempInt))
                        {
                            LogHelp.WriteLog("超越单" + dr["surpassbillname"].ToString() + "明细的工序信息有误");
                            continue;
                        }
                        dr["Specno"] = strSpec;
                        if (InsertSurpassBillMappingOfRow(dr) == false)
                        { continue; }
                        LogHelp.WriteLog("插入" + dr["containername"].ToString() + "工序号:" + strSpec + "超越单映射信息");
                    }
                }
            }
        }


        #endregion

        #region 删除已经超期无效的超越单映射记录
        /// <summary>
        /// 获取超期无效的超越单映射记录数据
        /// </summary>
        /// <returns></returns>
        DataTable GetContainerNoUseSurpass()
        {
            string strSql = @"
                            SELECT sm.specno,sm.containerid,cci.containername FROM containercurrentinfo cci
                            LEFT JOIN surpassbillmapping sm ON sm.containerid=cci.containerid --AND to_number(SM.SPECNO)=to_number(CCI.SPECNO)
                            LEFT JOIN SURPASSBILLDETAIL SD ON SD.SURPASSBILLDETAILID=SM.SURPASSBILLDETAILID
                            LEFT JOIN SURPASSBILLINFO SP ON SP.SURPASSBILLINFOID=SD.SURPASSBILLINFOID
                            WHERE SM.CONTAINERID IS NOT NULL AND SYSDATE>to_date(SP.EFFECTIVE,'yyyy-mm-dd hh24:mi:ss') 
                            ";
            DataSet ds = OracleHelper.Query(strSql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }
        /// <summary>
        /// 删除每条无效超期的映射记录
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        Boolean DeleteSurpassBillMappingOfRow(DataRow dr)
        {
            string deleteSql = @" DELETE surpassbillmapping sm WHERE sm.containerid=:containerid AND sm.specno=:specno";

            OracleParameter[] deletePara = new OracleParameter[2];
            deletePara[0] = OracleHelper.NewParameter(":Containerid", dr["Containerid"].ToString(), OracleType.VarChar);
            deletePara[1] = OracleHelper.NewParameter(":specno", dr["specno"].ToString(), OracleType.VarChar);

            int i = OracleHelper.ExecuteSql(deleteSql, deletePara);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除无效超期的超越单映射信息
        /// </summary>
        void DeleteSurpassBillMapping()
        {
            DataTable dt = GetContainerNoUseSurpass();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    if (!DeleteSurpassBillMappingOfRow(dr))
                    {
                        continue;
                    }
                    LogHelp.WriteLog("删除批次" + dr["Containerid"].ToString() + "工序号:" + dr["specno"].ToString() + "超越单映射信息");
                }
            }
        }
        #endregion

        #region 重复超越单
        public void DeleteRepeatSurpassBill()
        {
            string strSql = @"SELECT SURPASSBILLINFOID,ROWID
                              FROM (SELECT SURPASSBILLINFOID,ROWID,
                                           ROW_NUMBER() OVER(PARTITION BY SURPASSBILLINFOID ORDER BY CREATEDATE) CN
                                      FROM SURPASSBILLINFO) A
                             WHERE A.CN > 1";
            DataTable RepeatData = OracleHelper.Query(strSql).Tables[0];
            if (RepeatData.Rows.Count == 0)
            {
                return;
            }

            foreach (DataRow dr in RepeatData.Rows)
            {
                strSql = string.Format(@"delete SURPASSBILLINFO t WHERE t.ROWID='{0}'", dr["ROWID"]);
                OracleHelper.ExecuteSql(strSql);
            }

            strSql = @"SELECT SD.*, SM.CNT FROM (SELECT SD.*,
                    ROW_NUMBER() OVER(PARTITION BY SD.SURPASSBILLINFOID, SD.REMARK, SD.SURPASSOP, SD.SURPASSTYPE ORDER BY SD.SURPASSBILLINFOID) CN
                    FROM SURPASSBILLDETAIL SD) SD
                    LEFT JOIN (SELECT COUNT(*) CNT, SURPASSBILLDETAILID
                    FROM SURPASSBILLMAPPING   GROUP BY SURPASSBILLDETAILID) SM
                    ON SM.SURPASSBILLDETAILID = SD.SURPASSBILLDETAILID  WHERE SD.CN > 1";
            RepeatData = OracleHelper.Query(strSql).Tables[0];
            if (RepeatData.Rows.Count == 0)
            {
                return;
            }
            foreach (DataRow dr in RepeatData.Rows)
            {
                strSql = string.Format(@"delete SURPASSBILLDETAIL t WHERE t.SURPASSBILLDETAILID='{0}'", dr["SURPASSBILLDETAILID"]);
                OracleHelper.ExecuteSql(strSql);
                strSql = string.Format(@"delete  SURPASSBILLMAPPING t WHERE t.SURPASSBILLDETAILID='{0}'", dr["SURPASSBILLDETAILID"]);
                OracleHelper.ExecuteSql(strSql);
            }
        }
        #endregion

        #region 检测项特殊符号图片处理 add:Wangjh 20221229
        /// <summary>
        /// 特殊符号图片转化
        /// </summary>
        public void CheckPointToBase64()
        {
            string path = ConfigurationManager.AppSettings["UsingCheckImageSavePath"];
            string strReg = @"ImageTemp-(.)+?\.png";
            string newDisImg = @"<img src=""data:image/png;base64,{0}"">";
            //检测项，存储路径的数据统一更改为存储base64图片
            string strSql = @"select * from stepcheckpoint st 
where  TEMPDEALFAIL=0 and (st.checkpointnote is not null or st.drawingrequire is not null) and rownum<=1000 order by st.intime ";

            DataTable dt = OracleHelper.Query(strSql).Tables[0];

            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    updateTempParseRow(dr, path, strReg, newDisImg);
                }
                catch (Exception e)
                {
                    UFC.Common.LogHelp.WriteError("base64数据补齐，检测项", e);
                    OracleHelper.ExecuteSql("update stepcheckpoint st set TEMPDEALFAIL=1 where st.stepcheckpointid='" + dr["stepcheckpointid"].ToString() + "' ");//处理失败标记
                }
            }

            //检测项结果，存储路径数据统一更改为存储base64图片
            strSql = @"select * from stepcheckpointresult st 
where  NVL(ST.CREATEDATE,ST.CHECKDATE)>=SYSDATE-365 and TEMPDEALFAIL=0
AND(st.workcheckpointresultnote IS NOT NULL OR st.checkpointresultnote IS NOT NULL OR st.secondcheckpointnotedis IS NOT NULL)
and rownum<=1000";

            dt = OracleHelper.Query(strSql).Tables[0];

            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    updateTempParseResultRow(dr, path, strReg, newDisImg);
                }
                catch (Exception e)
                {
                    UFC.Common.LogHelp.WriteError("base64数据补齐，检测项结果", e);
                    OracleHelper.ExecuteSql("update stepcheckpointresult st set TEMPDEALFAIL=1 where st.stepcheckpointresultid='" + dr["stepcheckpointresultid"].ToString() + "' ");//处理失败标记
                }

            }
        }

        /// <summary>
        /// 单行处理,检测项
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="path"></param>
        /// <param name="strReg"></param>
        /// <param name="newDisImg"></param>
        void updateTempParseRow(DataRow dr, string path, string strReg, string newDisImg)
        {
            string oldDis = dr["checkpointnote"].ToString();
            string newDis = oldDis;
            var matchs = Regex.Matches(oldDis, strReg);
            if (!string.IsNullOrWhiteSpace(oldDis))
            {
                string _newDis = "";
                foreach (Match file in matchs)
                {

                    if (File.Exists(path + file.Value))
                    {
                        _newDis = string.Format(newDisImg, ParseImgToBase64(path + file.Value));
                    }
                    else//没有则再转换一遍
                    {
                        _newDis = DataAdjust.ParseCode3(dr["checkpointnote"].ToString());
                    }
                    newDis = newDis.Replace(string.Format(@"<img src=""\InSiteWebApplication\UsingCheckImage\{0}"">", file.Value), _newDis);
                    newDis = newDis.Replace(string.Format(@"<img src=""http://10.15.7.75/InSiteWebApplication\UsingCheckImage\{0}"">", file.Value), _newDis);
                }
                if (matchs.Count == 0)
                {
                    _newDis = DataAdjust.ParseCode3(dr["checkpointnote"].ToString());
                    newDis = _newDis;
                }
                //更新数据库
                int i = UpdatePointClobFiled("checkpointnotedis_b", newDis, dr["stepcheckpointid"].ToString());
                if (i < 1)
                {
                    OracleHelper.ExecuteSql("update stepcheckpoint st set TEMPDEALFAIL=1 where st.stepcheckpointid='" + dr["stepcheckpointid"].ToString() + "' ");//处理失败标记
                }
                else
                {
                    OracleHelper.ExecuteSql("update stepcheckpoint st set TEMPDEALFAIL=2 where st.stepcheckpointid='" + dr["stepcheckpointid"].ToString() + "' ");//处理成功标记
                    UFC.Common.LogHelp.WriteLog("stepcheckpoint.checkpointnotedis字段处理成功:" + dr["stepcheckpointid"].ToString());
                }

                //dis为空，补齐一下字段值
                if (string.IsNullOrWhiteSpace(dr["checkpointnotedis"].ToString()) && newDis.Length <= 4000)
                {
                    OracleHelper.ExecuteSql("update stepcheckpoint st set checkpointnotedis=q'(" + newDis + ")' where st.stepcheckpointid='" + dr["stepcheckpointid"].ToString() + "' ");
                }

            }
            //更新另一个字段
            if (!string.IsNullOrWhiteSpace(dr["drawingrequire"].ToString()))
            {
                oldDis = dr["drawingrequire"].ToString();
                newDis = oldDis;
                matchs = Regex.Matches(oldDis, strReg);
                string _newDis = "";
                foreach (Match file in matchs)
                {
                    if (File.Exists(path + file.Value))
                    {
                        _newDis = string.Format(newDisImg, ParseImgToBase64(path + file.Value));
                    }
                    else//没有则再转换一遍
                    {
                        _newDis = DataAdjust.ParseCode3(dr["drawingrequire"].ToString());
                    }
                    newDis = newDis.Replace(string.Format(@"<img src=""\InSiteWebApplication\UsingCheckImage\{0}"">", file.Value), _newDis);
                    newDis = newDis.Replace(string.Format(@"<img src=""http://10.15.7.75/InSiteWebApplication\UsingCheckImage\{0}"">", file.Value), _newDis);
                }
                if (matchs.Count == 0)
                {
                    _newDis = DataAdjust.ParseCode3(dr["drawingrequire"].ToString());
                    newDis = _newDis;
                }
                //更新数据库
                int i = UpdatePointClobFiled("drawingrequiredis_b", newDis, dr["stepcheckpointid"].ToString());

                if (i < 1)
                {
                    OracleHelper.ExecuteSql("update stepcheckpoint st set TEMPDEALFAIL=1 where st.stepcheckpointid='" + dr["stepcheckpointid"].ToString() + "' ");//处理失败标记
                }
                else
                {
                    OracleHelper.ExecuteSql("update stepcheckpoint st set TEMPDEALFAIL=2 where st.stepcheckpointid='" + dr["stepcheckpointid"].ToString() + "' ");//处理成功标记
                    UFC.Common.LogHelp.WriteLog("stepcheckpoint.drawingrequiredis字段处理成功:" + dr["stepcheckpointid"].ToString());

                }
                //dis为空，补齐一下字段值
                if (string.IsNullOrWhiteSpace(dr["drawingrequiredis"].ToString()) && newDis.Length <= 4000)
                {
                    OracleHelper.ExecuteSql("update stepcheckpoint st set drawingrequiredis=q'(" + newDis + ")' where st.stepcheckpointid='" + dr["stepcheckpointid"].ToString() + "' ");
                }
            }

        }

        /// <summary>
        /// 单行处理,检测项结果
        /// </summary>
        /// <param name="dr"></param>
        void updateTempParseResultRow(DataRow dr, string path, string strReg, string newDisImg)
        {
            string checkTemp = ConfigurationManager.AppSettings["CheckPointTempDir"];
            Boolean isSuccess = true;//处理成功或失败
            //更新字段workcheckpointresultnotedis
            if (!string.IsNullOrWhiteSpace(dr["workcheckpointresultnote"].ToString()) && string.IsNullOrWhiteSpace(dr["workcheckpointresultnotedis_b"].ToString()))
            {
                bool isLackImg = false;//是否缺失图片
                string oldDis = dr["workcheckpointresultnotedis"].ToString();
                if (string.IsNullOrWhiteSpace(oldDis))
                {
                    oldDis = dr["workcheckpointresultnote"].ToString();
                }
                oldDis = oldDis.Replace("IMG", "img");
                oldDis = oldDis.Replace(@"\", @"/");
                string newDis = oldDis;
                var matchs = Regex.Matches(oldDis, strReg);
                foreach (Match file in matchs)
                {
                    string _newDis = "";
                    if (File.Exists(path + file.Value))
                    {
                        _newDis = string.Format(newDisImg, ParseImgToBase64(path + file.Value));
                    }
                    else
                    {
                        isLackImg = true;
                        //如果正式路径不存在，可能存在临时路径下
                        if (File.Exists(checkTemp + file.Value))
                        {
                            _newDis = string.Format(newDisImg, ParseImgToBase64(checkTemp + file.Value));
                        }
                        else
                        {
                            _newDis = DataAdjust.ParseCode3(dr["workcheckpointresultnote"].ToString());
                        }
                    }
                    newDis = newDis.Replace(string.Format(@"<img src=""/InSiteWebApplication/UsingCheckImage/{0}"">", file.Value), _newDis);
                    newDis = newDis.Replace(string.Format(@"<img src=""http://10.15.7.75/InSiteWebApplication/UsingCheckImage/{0}"">", file.Value), _newDis);
                }
                //更新数据库
                int i = UpdatePointResultClobFiled("workcheckpointresultnotedis_b", newDis, dr["stepcheckpointresultid"].ToString());

                if (i < 1)
                {
                    isSuccess = false;
                    //OracleHelper.ExecuteSql("update stepcheckpointresult st set TEMPDEALFAIL=1 where st.stepcheckpointresultid='" + dr["stepcheckpointresultid"].ToString() + "' ");//处理失败标记
                }
                else
                {

                    //OracleHelper.ExecuteSql("update stepcheckpointresult st set TEMPDEALFAIL=2 where st.stepcheckpointresultid='" + dr["stepcheckpointresultid"].ToString() + "' ");//处理成功标记
                    UFC.Common.LogHelp.WriteLog("stepcheckpointresult.workcheckpointresultnotedis字段处理成功:" + dr["stepcheckpointresultid"].ToString());
                }
                //此时图片文件缺失，补齐一下字段值
                if (isLackImg && newDis.Length <= 4000)
                {
                    OracleHelper.ExecuteSql("update stepcheckpointresult st set workcheckpointresultnotedis='" + newDis + "' where st.stepcheckpointresultid='" + dr["stepcheckpointresultid"].ToString() + "' ");
                }
            }

            //更新字段secondcheckpointnotedis
            if (!string.IsNullOrWhiteSpace(dr["secondcheckpointnotedis"].ToString()) && string.IsNullOrWhiteSpace(dr["secondcheckpointnotedis_b"].ToString()))
            {
                string oldDis = dr["secondcheckpointnotedis"].ToString();
                oldDis = oldDis.Replace("IMG", "img");
                oldDis = oldDis.Replace(@"\", @"/");
                string newDis = oldDis;
                var matchs = Regex.Matches(oldDis, strReg);
                foreach (Match file in matchs)
                {
                    string _newDis = "";
                    if (File.Exists(path + file.Value))
                    {
                        _newDis = string.Format(newDisImg, ParseImgToBase64(path + file.Value));

                    }
                    else
                    {
                        //如果正式路径不存在，可能存在临时路径下
                        if (File.Exists(checkTemp + file.Value))
                        {
                            _newDis = string.Format(newDisImg, ParseImgToBase64(checkTemp + file.Value));
                        }
                        else
                        {
                            //_newDis = DataAdjust.ParseCode3(dr["secondcheckpointnote"].ToString());                        
                        }
                    }
                    newDis = newDis.Replace(string.Format(@"<img src=""/InSiteWebApplication/UsingCheckImage/{0}"">", file.Value), _newDis);
                    newDis = newDis.Replace(string.Format(@"<img src=""http://10.15.7.75/InSiteWebApplication/UsingCheckImage/{0}"">", file.Value), _newDis);
                }
                //更新数据库
                int i = UpdatePointResultClobFiled("secondcheckpointnotedis_b", newDis, dr["stepcheckpointresultid"].ToString());

                if (i < 1)
                {
                    isSuccess = false;
                    //OracleHelper.ExecuteSql("update stepcheckpointresult st set TEMPDEALFAIL=1 where st.stepcheckpointresultid='" + dr["stepcheckpointresultid"].ToString() + "' ");//处理失败标记
                }
                else
                {
                    //OracleHelper.ExecuteSql("update stepcheckpointresult st set TEMPDEALFAIL=2 where st.stepcheckpointresultid='" + dr["stepcheckpointresultid"].ToString() + "' ");//处理成功标记
                    UFC.Common.LogHelp.WriteLog("stepcheckpointresult.secondcheckpointnotedis字段处理成功:" + dr["stepcheckpointresultid"].ToString());
                }
            }

            //更新字段checkpointresultnotedis
            if (!string.IsNullOrWhiteSpace(dr["checkpointresultnote"].ToString()) && string.IsNullOrWhiteSpace(dr["checkpointresultnotedis_b"].ToString()))
            {
                bool isLackImg = false;//是否缺失图片
                string oldDis = dr["checkpointresultnotedis"].ToString();
                if (string.IsNullOrWhiteSpace(oldDis))
                {
                    oldDis = dr["checkpointresultnote"].ToString();
                }
                oldDis = oldDis.Replace("IMG", "img");
                oldDis = oldDis.Replace(@"\", @"/");
                string newDis = oldDis;
                var matchs = Regex.Matches(oldDis, strReg);
                foreach (Match file in matchs)
                {
                    string _newDis = "";
                    if (File.Exists(path + file.Value))
                    {
                        _newDis = string.Format(newDisImg, ParseImgToBase64(path + file.Value));
                    }
                    else
                    {
                        isLackImg = true;
                        //如果正式路径不存在，可能存在临时路径下
                        if (File.Exists(checkTemp + file.Value))
                        {
                            _newDis = string.Format(newDisImg, ParseImgToBase64(checkTemp + file.Value));
                        }
                        else
                        {
                            _newDis = DataAdjust.ParseCode3(dr["checkpointresultnote"].ToString());

                        }
                    }
                    newDis = newDis.Replace(string.Format(@"<img src=""/InSiteWebApplication/UsingCheckImage/{0}"">", file.Value), _newDis);
                    newDis = newDis.Replace(string.Format(@"<img src=""http://10.15.7.75/InSiteWebApplication/UsingCheckImage/{0}"">", file.Value), _newDis);
                }
                //更新数据库
                int i = UpdatePointResultClobFiled("checkpointresultnotedis_b", newDis, dr["stepcheckpointresultid"].ToString());

                if (i < 1)
                {
                    isSuccess = false;
                    //OracleHelper.ExecuteSql("update stepcheckpointresult st set TEMPDEALFAIL=1 where st.stepcheckpointresultid='" + dr["stepcheckpointresultid"].ToString() + "' ");//处理失败标记
                }
                else
                {
                    //OracleHelper.ExecuteSql("update stepcheckpointresult st set TEMPDEALFAIL=2 where st.stepcheckpointresultid='" + dr["stepcheckpointresultid"].ToString() + "' ");//处理成功标记
                    UFC.Common.LogHelp.WriteLog("stepcheckpointresult.checkpointresultnotedis字段处理成功:" + dr["stepcheckpointresultid"].ToString());
                }
                //此时图片文件缺失，补齐一下字段值
                if (isLackImg && newDis.Length <= 4000)
                {
                    OracleHelper.ExecuteSql("update stepcheckpointresult st set checkpointresultnotedis='" + newDis + "' where st.stepcheckpointresultid='" + dr["stepcheckpointresultid"].ToString() + "' ");
                }
            }
            //只要出现一个字段处理失败，标记为处理失败
            if (isSuccess)
            {
                OracleHelper.ExecuteSql("update stepcheckpointresult st set TEMPDEALFAIL=2 where st.stepcheckpointresultid='" + dr["stepcheckpointresultid"].ToString() + "' ");//处理成功标记
            }
            else
            {
                OracleHelper.ExecuteSql("update stepcheckpointresult st set TEMPDEALFAIL=1 where st.stepcheckpointresultid='" + dr["stepcheckpointresultid"].ToString() + "' ");//处理失败标记
            }

        }

        /// <summary>
        /// 根据id更新clob字段,stepcheckpoint表
        /// </summary>
        /// <param name="filedName"></param>
        /// <param name="filedValue"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        int UpdatePointClobFiled(string filedName, string filedValue, string id)
        {
            OracleParameter[] param = new OracleParameter[1];

            param[0] = new OracleParameter(filedName, OracleType.Clob);
            param[0].Direction = ParameterDirection.Input;
            param[0].Value = filedValue;

            string sql = "update stepcheckpoint st set " + filedName + "=:" + filedName + " where st.stepcheckpointid='" + id + "'";

            return OracleHelper.ExecuteSql(sql, param);
        }

        /// <summary>
        /// 根据id更新clob字段,stepcheckpointresult表
        /// </summary>
        /// <param name="filedName"></param>
        /// <param name="filedValue"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        int UpdatePointResultClobFiled(string filedName, string filedValue, string id)
        {
            OracleParameter[] param = new OracleParameter[1];

            param[0] = new OracleParameter(filedName, OracleType.Clob);
            param[0].Direction = ParameterDirection.Input;
            param[0].Value = filedValue;

            string sql = "update stepcheckpointresult st set " + filedName + "=:" + filedName + " where st.stepcheckpointresultid='" + id + "'";

            return OracleHelper.ExecuteSql(sql, param);
        }

        string ParseImgToBase64(string path)
        {
            Bitmap my = new Bitmap(path);

            using (MemoryStream me = new MemoryStream())
            {
                my.Save(me, my.RawFormat);

                var bytes = me.ToArray();

                return Convert.ToBase64String(bytes);
            }


        }

        #endregion

        #region 排产后未下发数据清理 add:Wangjh 202306

        public void ClearApsData()
        {
            var strSql = @"select ai.apsplaninfoid,ad.apsplandetailid from containercurrentinfo cci
inner join apsplaninfo ai on ai.containerid=cci.containerid 
inner join apsplandetail ad on ad.apsplaninfoid=ai.apsplaninfoid
where cci.isinstore=1 and ad.status not in (1,3) and rownum<1001";

            var dt = OracleHelper.Query(strSql).Tables[0];

            var apsIdDt = dt.DefaultView.ToTable(true, "apsplaninfoid");

            foreach (DataRow dr in apsIdDt.Rows)
            {
                var apsDetails = dt.Select("apsplaninfoid='" + dr["apsplaninfoid"].ToString() + "'");
                ClearApsDataRow(apsDetails);
            }

        }

        /// <summary>
        /// 清除排产数据按apsplaninfoid
        /// </summary>
        /// <param name="rows"></param>
        /// <returns></returns>
        Boolean ClearApsDataRow(DataRow[] rows)
        {
            var sqlList = new ArrayList();

            foreach (DataRow row in rows)
            {
                sqlList.Add(String.Format("delete apsplandetailemptime ae2 where ae2.apsplandetailempid in (select ae.apsplandetailempid from apsplandetailemp ae where ae.apsplandetailid='{0}')", row["apsplandetailid"].ToString()));
                sqlList.Add(String.Format("delete apsplandetailemp ae where ae.apsplandetailid='{0}'", row["apsplandetailid"].ToString()));
                sqlList.Add(String.Format("delete ApsPlanDetail al where al.apsplandetailid='{0}'", row["apsplandetailid"].ToString()));
            }
            OracleHelper.ExecuteSqlTran(sqlList);

            if (OracleHelper.GetSingle("select 1 from ApsPlanDetail al where al.apsplaninfoid='" + rows[0]["apsplaninfoid"].ToString() + "'") == null)
            {
                OracleHelper.ExecuteSql(string.Format("delete ApsPlanInfo where ApsPlanInfoid='" + rows[0]["apsplaninfoid"].ToString() + "'"));
            }

            return true;
        }

        #endregion


        #region 删除重复检测项

        /// <summary>
        /// 删除重复无效的检测项记录
        /// </summary>
        public void DeleteRepeat()
        {
            DataTable dt = GetRepeatInfo();
            foreach (DataRow dr in dt.Rows)
            {
                DeleteRepeatOfRow(dr);
            }
        }
        /// <summary>
        /// 获取重复的检测项记录
        /// </summary>
        /// <returns></returns>
        DataTable GetRepeatInfo()
        {
            string strSql = @"SELECT *  FROM (
                            SELECT sc.checkinputtype,sc.checkno,sc.checktype,sc.inputnum,sc.keycheck,sc.name,sc.qualitycheck,sc.workflowstepid,sc.tccheckpointnote,
                            sc.stepinfoid,sc.assemblytype,sc.TCDRAWINGREQUIRE,MAX(sc.intime) maxintime,COUNT(sc.name)
                            FROM stepcheckpoint sc
                            GROUP BY sc.checkinputtype,sc.checkno,sc.checktype,sc.inputnum,sc.keycheck,sc.name,sc.qualitycheck,sc.workflowstepid,sc.tccheckpointnote,
                            sc.stepinfoid,sc.assemblytype,sc.TCDRAWINGREQUIRE
                            HAVING COUNT(sc.workflowstepid)>1
                            ) WHERE ROWNUM<1000";
            return OracleHelper.Query(strSql).Tables[0];
        }
        /// <summary>
        /// 删除重复无效的检测项记录
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        Boolean DeleteRepeatOfRow(DataRow dr)
        {
            string strSql = string.Format(@"DELETE stepcheckpoint sc WHERE sc.workflowstepid='{0}' AND sc.intime<to_date('{1}','yyyy/mm/dd hh24:mi:ss')", dr["workflowstepid"], dr["maxintime"]);
            if (!dr.IsNull("checkinputtype"))
                strSql += " and sc.checkinputtype='" + dr["checkinputtype"] + "'";
            if (!dr.IsNull("checkno"))
                strSql += " and sc.checkno='" + dr["checkno"] + "'";
            if (!dr.IsNull("checktype"))
                strSql += " and sc.checktype='" + dr["checktype"] + "'";
            if (!dr.IsNull("inputnum"))
                strSql += " and sc.inputnum='" + dr["inputnum"] + "'";
            if (!dr.IsNull("keycheck"))
                strSql += " and sc.keycheck='" + dr["keycheck"] + "'";
            if (!dr.IsNull("name"))
                strSql += " and sc.name='" + dr["name"] + "'";
            if (!dr.IsNull("qualitycheck"))
                strSql += " and sc.qualitycheck='" + dr["qualitycheck"] + "'";
            if (!dr.IsNull("workflowstepid"))
                strSql += " and sc.workflowstepid='" + dr["workflowstepid"] + "'"; 
            if (!dr.IsNull("stepinfoid"))
                strSql += " and sc.stepinfoid='" + dr["stepinfoid"] + "'";
            if (!dr.IsNull("assemblytype"))
                strSql += " and sc.assemblytype='" + dr["assemblytype"] + "'"; 

            strSql += " and sc.TCDRAWINGREQUIRE " + (dr.IsNull("TCDRAWINGREQUIRE") ? " is null" : "=:TCDRAWINGREQUIRE");
            strSql += " and sc.tccheckpointnote " + (dr.IsNull("tccheckpointnote") ? " is null" : "=:tccheckpointnote");

            List<OracleParameter> deleteParaList = new List<OracleParameter>();
            if (!dr.IsNull("TCDRAWINGREQUIRE"))
            {
                deleteParaList.Add(OracleHelper.NewParameter(":TCDRAWINGREQUIRE", dr["TCDRAWINGREQUIRE"].ToString(), OracleType.VarChar));
            }
            if (!dr.IsNull("tccheckpointnote"))
            {
                deleteParaList.Add(OracleHelper.NewParameter(":tccheckpointnote", dr["tccheckpointnote"].ToString(), OracleType.VarChar));
            }

            int i = OracleHelper.ExecuteSql(strSql, deleteParaList.ToArray());
            if (i > 0)
                return true;
            else
                return false;
        }

        #endregion

        #region 删除检测项临时文件

        public void deleteCheckPointTemp()
        {
            string checkTemp = ConfigurationManager.AppSettings["CheckPointTempDir"];
            DirectoryInfo mydir = new DirectoryInfo(checkTemp);
            foreach (FileInfo fi in mydir.GetFiles())
            {
                TimeSpan i = DateTime.Now.Subtract(fi.LastWriteTime);
                if (i.Days < 30)
                {
                    continue;
                }
                fi.Delete();
            }
        }
        #endregion

        #region 删除无效批次派报检数据
        public void InvalidContainerStepData()
        {
            //InvalidContainerDispatch();
            //InvalidContainerWorkreport();
            //InvalidContainerCheck();
        }

        private void InvalidContainerDispatch()
        {
            string strSql = @"  SELECT cd.containerstepdispatchid FROM containerstepdispatch cd
                      LEFT JOIN container c ON c.containerid=cd.containerid
                      WHERE   c.containerid=c.containername  AND cd.isuse=0 AND ROWNUM<100 ";
            DataTable dt = OracleHelper.Query(strSql).Tables[0];
            List<object[]> sqllist = new List<object[]>();
            string updateSql = string.Empty;
            foreach (DataRow dr in dt.Rows)
            {
                updateSql = string.Format("Update containerstepdispatch Set Isuse=1 where containerstepdispatchid='{0}'", dr["containerstepdispatchid"]);
                sqllist.Add(new object[] { updateSql, null });
            }
            if (sqllist.Count > 0)
            {
                OracleHelper.ExecuteSqlTran(sqllist);
            }
        }

        private void InvalidContainerWorkreport()
        {
            string strSql = @"  SELECT cd.containerstepworkreportid FROM containerstepworkreport cd
                      LEFT JOIN container c ON c.containerid=cd.containerid
                      WHERE   c.containerid=c.containername  AND cd.isuse=0 AND ROWNUM<100 ";
            DataTable dt = OracleHelper.Query(strSql).Tables[0];
            List<object[]> sqllist = new List<object[]>();
            string updateSql = string.Empty;
            foreach (DataRow dr in dt.Rows)
            {
                updateSql = string.Format("Update containerstepworkreport Set Isuse=1 where containerstepworkreportid='{0}'", dr["containerstepworkreportid"]);
                sqllist.Add(new object[] { updateSql, null });
            }
            if (sqllist.Count > 0)
            {
                OracleHelper.ExecuteSqlTran(sqllist);
            }
        }

        private void InvalidContainerCheck()
        {
            string strSql = @"  SELECT cd.containercheckinfoid FROM containercheckinfo cd
                      LEFT JOIN container c ON c.containerid=cd.containerid
                      WHERE   c.containerid=c.containername  AND cd.isuse=0 AND ROWNUM<100 ";
            DataTable dt = OracleHelper.Query(strSql).Tables[0];
            List<object[]> sqllist = new List<object[]>();
            string updateSql = string.Empty;
            foreach (DataRow dr in dt.Rows)
            {
                updateSql = string.Format("Update containercheckinfo Set Isuse=1 where containercheckinfoid='{0}'", dr["containercheckinfoid"]);
                sqllist.Add(new object[] { updateSql, null });
            }
            strSql = @"  SELECT cd.containerfinishedcheckinfoid FROM containerfinishedcheckinfo cd
                      LEFT JOIN container c ON c.containerid=cd.containerid
                      WHERE   c.containerid=c.containername  AND cd.isuse=0 AND ROWNUM<500 ";
            dt = OracleHelper.Query(strSql).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                updateSql = string.Format("Update containerfinishedcheckinfo Set Isuse=1 where containerfinishedcheckinfoid='{0}'", dr["containerfinishedcheckinfoid"]);
                sqllist.Add(new object[] { updateSql, null });
            }
            if (sqllist.Count > 0)
            {
                OracleHelper.ExecuteSqlTran(sqllist);
            }
        }

        #endregion

        #region documentset,document专项处理 add:Wangjh 20211105
        //已有附件的对象路径，全部结转到objectdocinfo，然后删除数据库中的附件
        public void DealDocumentInfo()
        {
            //查询documentset数据
            string strSql = @"  SELECT ds.documentsetid,null Revision, ds.documentsetname DocName,'DocumentSetMaintDoc' TxnDocName ,'DocumentSetMaint' TxnName,'NDO' CDOType,0 IsRevofrcd,p.productid,s.specid,w.workflowid FROM documentset ds
                            LEFT JOIN product p ON p.documentsetid=ds.documentsetid
                            LEFT JOIN spec s ON s.documentsetid=ds.documentsetid
                            LEFT JOIN workflow w ON w.documentsetid=ds.documentsetid
                            LEFT JOIN CHANGESTATUS CH ON CH.CHANGESTATUSID=DS.CHANGESTATUSID
                            WHERE ds.documentsetid IS NOT NULL AND (p.documentsetid IS NOT NULL OR w.documentsetid IS NOT NULL OR s.documentsetid IS NOT NULL)
                            AND w.workflowid='4800268000019935'
                           -- AND CH.LASTCHANGEDATE>SYSDATE-1 
                            
                            AND ROWNUM<51  ";

            DataTable dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count == 0)
                return;


            External.APICalling.CamstarClientAPI api = new External.APICalling.CamstarClientAPI(ConfigurationManager.AppSettings["CamstarUserName"],
               ConfigurationManager.AppSettings["CamstarPassword"]);

            foreach (DataRow dr in dt.Rows)
            {

                string parentId = "";
                if (!string.IsNullOrWhiteSpace(dr["productid"].ToString()))
                {
                    parentId = dr["productid"].ToString();
                }
                else if (!string.IsNullOrWhiteSpace(dr["specid"].ToString()))
                {
                    parentId = dr["specid"].ToString();
                }
                else if (!string.IsNullOrWhiteSpace(dr["workflowid"].ToString()))
                {
                    parentId = dr["workflowid"].ToString();
                }
                //查询document数据并删除
                strSql = @"SELECT doc.documentrevision Revision,dtb2.documentname DocName,'DocumentMaintDoc' TxnDocName ,'DocumentMaint' TxnName,'RO' CDOType,(CASE WHEN dtb2.revofrcdid=doc.documentid THEN 1 ELSE 0 END) IsRevofrcd,doc.documentid,
REPLACE( SUBSTR(doc.identifier,INSTR(doc.Identifier,'Temp\')),'\','/') filepath   FROM documentset ds
  LEFT JOIN Documententry dt ON dt.documentsetid=ds.documentsetid
                    LEFT JOIN DOCUMENTbase dtb ON dtb.documentbaseid=dt.documentbaseid
                    LEFT JOIN DOCUMENT doc ON doc.documentid=NVL(dtb.revofrcdid,dt.documentid)
                    LEFT JOIN  DOCUMENTbase dtb2 ON dtb2.documentbaseid=doc.documentbaseid                    
                    WHERE   ds.documentsetid='{0}'";
                strSql = string.Format(strSql, dr["documentsetid"].ToString());
                DataTable docDt = OracleHelper.Query(strSql).Tables[0];
                if (docDt.Rows.Count > 0)
                {
                    //结转到objectdocinfo
                    strSql = "select * from objectdocinfo t where t.parentid='" + parentId + "' ";
                    DataTable objDocDt = OracleHelper.Query(strSql).Tables[0];
                    if (objDocDt.Rows.Count == 0)
                    {
                        foreach (DataRow docDr in docDt.Rows)
                        {
                            SaveObjectDocInfo(parentId, docDr["filepath"].ToString(), docDr["DocName"].ToString());
                        }
                    }
                    else
                    {
                        foreach (DataRow docDr in docDt.Rows)
                        {
                            //已经结转了,删除多余的temp底下的文件
                            string path = docDr["filepath"].ToString();
                            path = path.Substring(0, path.LastIndexOf("/"));
                            path = @"D:\Program Files (x86)\Camstar\InSite Web Application\InSiteWebApplication\" + path;
                            try
                            {
                                //暂存需要删除的无效附件路径
                                OracleHelper.ExecuteSql(string.Format("INSERT INTO wjhTempTable (PATH,parentid) VALUES ('{0}','{1}')", path, parentId));

                                // DirectoryInfo di = new DirectoryInfo(path);
                                //  di.Delete(true);

                            }
                            catch (Exception ex)
                            {
                            }
                        }
                    }
                    foreach (DataRow docDr in docDt.Rows)
                    {
                        //删除document
                        DeleteNoUseDocOfRow(docDr, api);
                    }
                }
                //删除documentset
                DeleteNoUseDocOfRow(dr, api);
            }
        }

        /// <summary>
        /// 保存对象文档信息数据
        /// add:Wangjh 20211101
        /// </summary>
        /// <param name="parentid"></param>
        /// <param name="filepath"></param>
        /// <param name="filename"></param>
        /// <returns></returns>
        public bool SaveObjectDocInfo(string parentid, string filepath, string filename)
        {
            string strSql = @"INSERT INTO OBJECTDOCINFO ob (ob.objectdocinfoid,ob.parentid,ob.filepath,ob.filename)
VALUES(sys_guid(),'{0}','{1}','{2}')";

            strSql = string.Format(strSql, parentid, filepath, filename);

            int i = OracleHelper.ExecuteSql(strSql);
            if (i > 0)
                return true;
            else
                return false;
        }

        #endregion

        #region 清除班组人员中，重复的人员

        /// <summary>
        /// 查出有重复的记录
        /// </summary>
        /// <returns></returns>
        DataTable GetRepeateTeamEmp()
        {
            string strSql = @"select distinct teamid,teamname from (
select te.teamid,t.teamname,te.employeeid from teamemployee te
left join team t on t.teamid=te.teamid
left join employee e on e.employeeid=te.employeeid
group by te.teamid,t.teamname,te.employeeid
having count(te.employeeid)>1
) where rownum<50";
            DataTable dt = OracleHelper.Query(strSql).Tables[0];
            return dt;
        }

        /// <summary>
        /// 处理单个班组的重复人员
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="i"></param>
        Boolean DealSingleTeamEmp(DataTable dt, int i, ref string strMsg)
        {
            var strSql = @"select te.teamid,te.employeeid,t.teamname,e.employeename from teamemployee te
left join team t on t.teamid=te.teamid
left join employee e on e.employeeid=te.employeeid
where t.teamid='{0}'";
            DataTable dtTeam = OracleHelper.Query(string.Format(strSql, dt.Rows[i]["teamid"].ToString())).Tables[0];

            External.APICalling.CamstarClientAPI api = new External.APICalling.CamstarClientAPI(ConfigurationManager.AppSettings["CamstarUserName"],
              ConfigurationManager.AppSettings["CamstarPassword"]);


            var p_dataEntityList = new List<ClientAPIEntity>();
            p_dataEntityList.Add(new ClientAPIEntity("Name", DataTypeEnum.DataField, dt.Rows[i]["teamname"].ToString(), ""));

            var putedEmployee = new List<string>();
            for (int j = 0; j < dtTeam.Rows.Count; j++)
            {
                if (putedEmployee.Contains(dtTeam.Rows[j]["employeename"].ToString()))
                {
                    continue;
                }
                else
                {
                    putedEmployee.Add(dtTeam.Rows[j]["employeename"].ToString());
                }

            }

            p_dataEntityList.Add(new ClientAPIEntity("Employee", DataTypeEnum.NamedObjectList, putedEmployee, "", dtTeam.Rows.Count.ToString(), false));

            //return api.UpdateModelHasSubentityList("TeamMaintDoc", "TeamMaint", dt.Rows[i]["teamname"].ToString(), "", "", p_dataEntityList, "Employee",p_subdataEntityList,dtTeam .Rows .Count ,ref strMsg);
            var re = api.UpdateModelByStandard("TeamMaintDoc", "TeamMaint", dt.Rows[i]["teamname"].ToString(), "", p_dataEntityList, ref strMsg);
            strMsg = "班组：" + dt.Rows[i]["teamname"].ToString() + " " + strMsg;
            return re;
        }

        /// <summary>
        /// 处理班组中重复的人员
        /// </summary>
        public void DealTeamEmp()
        {
            string strmsg = "";
            DataTable dt = GetRepeateTeamEmp();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (!DealSingleTeamEmp(dt, i, ref strmsg))
                {
                    LogHelp.WriteError("处理班组中重复人员报错：" + strmsg, null);
                }
            }
        }

        #endregion

        #region 清除人员中，重复的设备
        /// <summary>
        /// 查出有重复的记录
        /// </summary>
        /// <returns></returns>
        DataTable GetRepeateEmpRes()
        {
            string strSql = @"select distinct employeeid from (
select el.employeeid,el.resourcelistid from employeeresourcelist el
group by el.employeeid,el.resourcelistid
having count(el.resourcelistid)>1
) where rownum<=50";
            DataTable dt = OracleHelper.Query(strSql).Tables[0];
            return dt;
        }

        /// <summary>
        /// 处理单个人员中重复的设备
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="i"></param>
        /// <param name="strMsg"></param>
        /// <returns></returns>
        Boolean DealSingleEmpRes(DataTable dt, int i, ref string strMsg)
        {
            var strSql = @"select el.employeeid,e.employeename,el.resourcelistid,rd.resourcename from employeeresourcelist el
left join employee e on e.employeeid=el.employeeid
left join resourcedef rd on rd.resourceid=el.resourcelistid
where el.employeeid='{0}'";
            DataTable dtRes = OracleHelper.Query(string.Format(strSql, dt.Rows[i]["employeeid"].ToString())).Tables[0];

            External.APICalling.CamstarClientAPI api = new External.APICalling.CamstarClientAPI(ConfigurationManager.AppSettings["CamstarUserName"],
               ConfigurationManager.AppSettings["CamstarPassword"]);

            var p_dataEntityList = new List<ClientAPIEntity>();
            p_dataEntityList.Add(new ClientAPIEntity("Name", DataTypeEnum.DataField, dtRes.Rows[0]["employeename"].ToString(), ""));

            var putedRes = new List<string>();
            for (int j = 0; j < dtRes.Rows.Count; j++)
            {
                if (string.IsNullOrWhiteSpace(dtRes.Rows[j]["resourcename"].ToString()))
                    continue;
                if (putedRes.Contains(dtRes.Rows[j]["resourcename"].ToString()))
                {
                    continue;
                }
                else
                {
                    putedRes.Add(dtRes.Rows[j]["resourcename"].ToString());
                }

            }

            p_dataEntityList.Add(new ClientAPIEntity("ResourceList", DataTypeEnum.NamedObjectList, putedRes, "", dtRes.Rows.Count.ToString(), false));

            //return api.UpdateModelHasSubentityList("TeamMaintDoc", "TeamMaint", dt.Rows[i]["teamname"].ToString(), "", "", p_dataEntityList, "Employee",p_subdataEntityList,dtTeam .Rows .Count ,ref strMsg);
            var re = api.UpdateModelByStandard("EmployeeMaintDoc", "EmployeeMaint", dtRes.Rows[0]["employeename"].ToString(), "", p_dataEntityList, ref strMsg);
            strMsg = "人员：" + dtRes.Rows[0]["employeename"].ToString() + " " + strMsg;
            return re;

        }

        /// <summary>
        /// 处理人员中重复设备
        /// </summary>
        public void DealEmpRes()
        {
            string strmsg = "";
            DataTable dt = GetRepeateEmpRes();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (!DealSingleEmpRes(dt, i, ref strmsg))
                {
                    LogHelp.WriteError("处理人员中重复设备：" + strmsg, null);
                }
            }
        }

        #endregion

        #region 删除无效的派报检记录，二年前

        /// <summary>
        /// 处理无效的派报检记录
        /// </summary>
        public void DeleteNoUseProcessing()
        {
            ArrayList sqlList = new ArrayList();
            //删除无效派工记录
            DataTable dt = OracleHelper.Query(@"select * from containerstepdispatch csd where csd.createdate<=sysdate-721 and csd.isuse=1 and rownum<501").Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                sqlList.Add(string.Format(@"delete containerstepdispatch where containerstepdispatchid='{0}'", dr["containerstepdispatchid"].ToString()));
                sqlList.Add(string.Format(@"delete containerstepdispatchdetail where containerstepdispatchid='{0}'", dr["containerstepdispatchid"].ToString()));
            }
            OracleHelper.ExecuteSqlTran(sqlList);
            sqlList.Clear();

            dt = OracleHelper.Query(@"select * from containerstepdispatch2 csd where csd.createdate<=sysdate-721 and csd.isuse=1 and rownum<501").Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                sqlList.Add(string.Format(@"delete containerstepdispatch2 where containerstepdispatchid='{0}'", dr["containerstepdispatchid"].ToString()));
                sqlList.Add(string.Format(@"delete containerstepdispatchdetail2 where containerstepdispatchid='{0}'", dr["containerstepdispatchid"].ToString()));
            }
            OracleHelper.ExecuteSqlTran(sqlList);
            sqlList.Clear();
            //删除无效报工记录
            dt = OracleHelper.Query(@"select * from containerstepworkreport csd where csd.createdate<=sysdate-721 and csd.isuse=1 and rownum<501").Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                sqlList.Add(string.Format(@"delete containerstepworkreport where containerstepworkreportid='{0}'", dr["containerstepworkreportid"].ToString()));
                sqlList.Add(string.Format(@"delete containerstepworkreportchild where containerstepworkreportid='{0}'", dr["containerstepworkreportid"].ToString()));
            }
            OracleHelper.ExecuteSqlTran(sqlList);
            sqlList.Clear();

            dt = OracleHelper.Query(@"select * from containerstepworkreport2 csd where csd.createdate<=sysdate-721 and csd.isuse=1 and rownum<501").Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                sqlList.Add(string.Format(@"delete containerstepworkreport2 where containerstepworkreportid='{0}'", dr["containerstepworkreportid"].ToString()));
                sqlList.Add(string.Format(@"delete containerstepworkreportchild2 where containerstepworkreportid='{0}'", dr["containerstepworkreportid"].ToString()));
            }
            OracleHelper.ExecuteSqlTran(sqlList);
            sqlList.Clear();
            //删除无效检验记录
            dt = OracleHelper.Query(@"select * from containercheckinfo csd where csd.createdate<=sysdate-721 and csd.isuse=1 and rownum<501").Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                sqlList.Add(string.Format(@"delete containercheckinfo where containercheckinfoid='{0}'", dr["containercheckinfoid"].ToString()));
            }
            OracleHelper.ExecuteSqlTran(sqlList);
            sqlList.Clear();

            dt = OracleHelper.Query(@"select * from containercheckinfo2 csd where csd.createdate<=sysdate-721 and csd.isuse=1 and rownum<501").Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                sqlList.Add(string.Format(@"delete containercheckinfo2 where containercheckinfoid='{0}'", dr["containercheckinfoid"].ToString()));
            }
            OracleHelper.ExecuteSqlTran(sqlList);
            sqlList.Clear();

            dt = OracleHelper.Query(@"select * from containerfinishedcheckinfo csd where csd.createdate<=sysdate-721 and csd.isuse=1 and rownum<501").Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                sqlList.Add(string.Format(@"delete containerfinishedcheckinfo where containerfinishedcheckinfoid='{0}'", dr["containerfinishedcheckinfoid"].ToString()));
                sqlList.Add(string.Format(@"delete containerfinishedcheckdetail where containerfinishedcheckinfoid='{0}'", dr["containerfinishedcheckinfoid"].ToString()));
            }
            OracleHelper.ExecuteSqlTran(sqlList);
            sqlList.Clear();

        }

        #endregion

        #region 热表报工但没有派工ID的数据 add by zhangrj 20240318

        /// <summary>
        /// 获取热表报工但没有派工ID的数据
        /// </summary>
        /// <returns></returns>
        DataTable GetRBReportNoDispatch()
        {
            string sql = @"SELECT c.containername,c.containerid,csw.workflowstepid,csw.containerstepworkreportid FROM containerstepworkreport csw 
                       left JOIN container c ON c.containerid = csw.containerid
                       WHERE csw.isuse = 0 AND csw.containerstepdispatchid IS NULL AND csw.checktype = 2 AND csw.factoryid = '0004e2800000281c'
                       AND c.status = 1 
                       AND EXISTS( SELECT 1 FROM containerstepdispatch csd WHERE csd.containerid = csw.containerid AND csd.workflowstepid = csw.workflowstepid AND csd.isuse =0 )";
            DataSet ds = OracleHelper.Query(sql);
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }
        /// <summary>
        /// 处理热表工序报工异常数据
        /// </summary>
        public void DealRBReportStepRecord()
        {
            DataTable RepeatData = GetRBReportNoDispatch();
            if (RepeatData.Rows.Count == 0)
            {
                return;
            }

            foreach (DataRow dr in RepeatData.Rows)
            {
                ArrayList list = new ArrayList();

                string strSql = string.Format(@" SELECT csd.containerstepdispatchid,csd.qty,csd.employeeid,to_char(csd.createdate,'yyyy-mm-dd hh24:mi:ss') createdate,e.fullname 
                                FROM containerstepdispatch csd
                                LEFT JOIN employee e ON e.employeeid = csd.employeeid
                                WHERE csd.containerid = '{0}' AND csd.workflowstepid = '{1}' AND csd.isuse = 0", dr["containerid"].ToString(), dr["workflowstepid"].ToString());

                DataTable dtR = OracleHelper.Query(strSql).Tables[0];
                if (dtR.Rows.Count > 0)
                {
                    DataRow item = dtR.Rows[0];
                    list.Add(string.Format(" UPDATE containerstepworkreport csw SET csw.containerstepdispatchid = '{0}' WHERE csw.containerstepworkreportid = '{1}'", item["containerstepdispatchid"].ToString(), dr["containerstepworkreportid"].ToString()));
                    list.Add(string.Format("DELETE containersteprecord csr WHERE csr.containerid = '{0}' AND csr.workflowstepid = '{1}' AND csr.containersteprecordid IS NULL", dr["containerid"].ToString(), dr["workflowstepid"].ToString()));
                    list.Add(string.Format(@"UPDATE containersteprecord csr SET csr.dispatchid = '{0}',
                    csr.dispatchqty = '{1}',csr.dispatchdate = to_date('{2}','yyyy-mm-dd hh24:mi:ss'),csr.dispatchemployeeid='{3}',csr.dispatchemployeename='{4}' 
                    WHERE csr.containerid = '{5}' AND csr.workflowstepid = '{6}'", item["containerstepdispatchid"].ToString(), item["qty"].ToString(), item["createdate"].ToString(), item["employeeid"].ToString(), item["fullname"].ToString(), dr["containerid"].ToString(), dr["workflowstepid"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(list);
            }
        }
        #endregion
    }
}